﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace MDSAdministration
{
    public enum DeploymentBuildOrder
    {
        Master_CheckPendingCount = 0,
        Slave_DeleteAttributeDomain = 1,
        Master_DeleteAttribute = 2,

        Master_DeleteArticle = 3,
        Slave_DeleteArticle = 4,
        Slave_DeleteArticleCheck =5,
        Master_DeleteEntity = 6,
        Slave_DeleteEntity = 7,

        Master_AddEntity = 8,
        Slave_AddEntity = 9,

        Master_JobStop = 10,
        Slave_JobStop = 11,
        
        Master_AddArticle = 12,
        Slave_AddArticle = 13,
        Master_JobStart = 14,
        Slave_JobStart = 15,
        Master_JobReplicationCheck = 16,

        Master_ChangeEntity = 17,
        Slave_ChangeEntity = 18,

        Master_AddChangeAttribute = 19,
        Slave_AddChangeAttribute = 20,

        Master_AddChangeDeleteView = 21,
        Slave_AddChangeDeleteView = 22,

        //used when not replicated or for non schema changes
        Master = 99
    }


    public class DeploymentBuilder
    {
        public ServerMDSDetails Server { get; set; }
        public List<ActionAttribute> LActionAttributes;
        public List<ActionAttributeGroup> LActionAttributeGroups;
        public List<ActionAttributeGroupDetail> LActionAttributeGroupDetails;
        public List<ActionView> LActionViews;
        public List<ActionEntity> LActionEntities;
        public List<ActionEntityMember> LActionEntityMembers;
        public string sqlStatement = "";
        public string PublicationName { get; set; }
        public bool IsReplicated { get; set; }
        private bool IsAddMasterAtricle { get; set; }
        private bool IsAddSlaveAtricle { get; set; }

        private bool IsSlaveView { get; set; }
        


        bool IsAttributeChanged { get; set; }
        
        public String CurrentUser { get; set; }
        
        public DeploymentBuilder()
        {
            LActionEntities = new List<ActionEntity>();
            LActionAttributes = new List<ActionAttribute>();
            LActionAttributeGroups = new List<ActionAttributeGroup>();
            LActionAttributeGroupDetails = new List<ActionAttributeGroupDetail>();
            LActionViews = new List<ActionView>();
            LActionEntityMembers = new List<ActionEntityMember>();
            IsAddMasterAtricle = false;
            IsAddSlaveAtricle = false;
            IsSlaveView = false;
        }

        public Dictionary<DeploymentBuildOrder, DeploymentStep> Build()
        {

            Dictionary<DeploymentBuildOrder, DeploymentStep> dChanges = new Dictionary<DeploymentBuildOrder, DeploymentStep>();

            foreach (var step in Enum.GetValues(typeof(DeploymentBuildOrder)).Cast<DeploymentBuildOrder>())
            {
                dChanges.Add(step, new DeploymentStep(){StepName = step.ToString()});
            }



            dChanges[DeploymentBuildOrder.Master_CheckPendingCount].IsMaster = true;
            dChanges[DeploymentBuildOrder.Slave_DeleteAttributeDomain].IsMaster = false;
            dChanges[DeploymentBuildOrder.Master_DeleteAttribute].IsMaster = true;
            dChanges[DeploymentBuildOrder.Master_DeleteArticle].IsMaster = true;
            dChanges[DeploymentBuildOrder.Slave_DeleteArticle].IsMaster = false;
            dChanges[DeploymentBuildOrder.Slave_DeleteArticleCheck].IsMaster = false;
            dChanges[DeploymentBuildOrder.Master_DeleteEntity].IsMaster = true;
            dChanges[DeploymentBuildOrder.Slave_DeleteEntity].IsMaster = false;
            dChanges[DeploymentBuildOrder.Master_JobStop].IsMaster = true;
            dChanges[DeploymentBuildOrder.Slave_JobStop].IsMaster = false;
            dChanges[DeploymentBuildOrder.Master_AddEntity].IsMaster = true;
            dChanges[DeploymentBuildOrder.Slave_AddEntity].IsMaster = false;
            dChanges[DeploymentBuildOrder.Master_AddArticle].IsMaster = true;
            dChanges[DeploymentBuildOrder.Slave_AddArticle].IsMaster = false;
            dChanges[DeploymentBuildOrder.Master_JobStart].IsMaster = true;
            dChanges[DeploymentBuildOrder.Slave_JobStart].IsMaster = false;
            dChanges[DeploymentBuildOrder.Master_JobReplicationCheck].IsMaster = true;
            dChanges[DeploymentBuildOrder.Master_AddChangeAttribute].IsMaster = true;
            dChanges[DeploymentBuildOrder.Slave_AddChangeAttribute].IsMaster = false;
            dChanges[DeploymentBuildOrder.Master_AddChangeDeleteView].IsMaster = true;
            dChanges[DeploymentBuildOrder.Slave_AddChangeDeleteView].IsMaster = false;

            dChanges[DeploymentBuildOrder.Master].IsMaster = true;

            buildEntities(dChanges);
            buildAttributes(dChanges);
            buildViews(dChanges);

            buildAttributeGroups(dChanges[DeploymentBuildOrder.Master].Query);
            buildAttributeGroupDetails(dChanges[DeploymentBuildOrder.Master].Query);
            
            buildMembers(dChanges[DeploymentBuildOrder.Master].Query);
            buildCompletMessage(dChanges[DeploymentBuildOrder.Master].Query);
            dChanges[DeploymentBuildOrder.Master].Query.Append(sqlStatement);

            buildReplicationCheckPendingCount(dChanges);

            return dChanges;
        }

        private void buildReplicationCheckPendingCount(Dictionary<DeploymentBuildOrder, DeploymentStep> dChanges)
        {
            if (!IsReplicated)
            {
                return;
            }

            StringBuilder sb = new StringBuilder();
            ReplicationCheckRequestResponse(sb);

            foreach (var change in dChanges.Values)
            {
                if (change.StepName == DeploymentBuildOrder.Master_JobStop.ToString() ||
                    change.StepName == DeploymentBuildOrder.Master_JobStart.ToString() ||
                    change.StepName == DeploymentBuildOrder.Slave_JobStop.ToString() ||
                    change.StepName == DeploymentBuildOrder.Slave_JobStart.ToString() ||
                    change.StepName == DeploymentBuildOrder.Master_AddArticle.ToString() ||
                    change.StepName == DeploymentBuildOrder.Slave_DeleteArticle.ToString() ||
                    change.StepName == DeploymentBuildOrder.Slave_AddArticle.ToString())
                {
                    continue;
                }
                
                if (change.IsMaster == false && !String.IsNullOrEmpty(change.Query.ToString()))
                {
                    change.Query.Insert(0, sb.ToString());
                    change.Query.Append(sb.ToString());
                }
                else if (change.StepName == DeploymentBuildOrder.Master_DeleteArticle.ToString() && !String.IsNullOrEmpty(change.Query.ToString()))
                {
                    change.Query.Insert(0, sb.ToString());
                }
                else if (change.StepName == DeploymentBuildOrder.Master_JobReplicationCheck.ToString())
                {
                    change.Query.Insert(0, sb.ToString());
                }
                else if (change.StepName == DeploymentBuildOrder.Slave_DeleteArticleCheck.ToString()
                    && !String.IsNullOrEmpty(dChanges[DeploymentBuildOrder.Slave_DeleteArticle].Query.ToString()))
                {
                    change.Query.Append(sb.ToString());
                }
                else if (change.IsMaster == true && !String.IsNullOrEmpty(change.Query.ToString()) && change.StepName != DeploymentBuildOrder.Master.ToString())
                {
                    change.Query.Insert(0, sb.ToString());
                    change.Query.Append(sb.ToString());
                }
            }
        }

        private void buildCompletMessage( StringBuilder sb)
        {
            sb.Append("PRINT 'Deployment Complete.'\r\n");
        }

        #region Attributes
        private void buildAttributes(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB)
        {
            IsAttributeChanged = false;
            foreach (var a in LActionAttributes)
            {   
                switch (a.status)
                {
                        case ObjectStatus.Missing:
                        {
                            buildAttributeMissing(dSB, a);
                        }
                        break;
                        case ObjectStatus.Different:
                        {
                            buildAttributeDifferent(dSB, a);
                        }
                        break;
                        case ObjectStatus.SortOrder:
                        {
                            buildAttributeSort(dSB[DeploymentBuildOrder.Master_AddChangeAttribute].Query, a);
                        }
                        break;
                        case ObjectStatus.Rename:
                        {
                            buildAttributeRename(dSB[DeploymentBuildOrder.Master_AddChangeAttribute].Query, a, true);
                        }
                        break;
                        case ObjectStatus.Delete:
                        {
                            buildAttributeDelete(dSB, a);
                        }
                        break;
                    default:
                        throw new NotImplementedException();
                }
            }

            if (IsAttributeChanged)
            {   
                buildRegenerateViewsAndStoredProcedures(dSB[DeploymentBuildOrder.Master_AddChangeAttribute].Query);
                dSB[DeploymentBuildOrder.Master_AddChangeAttribute].Query.Append("GO\r\n");
            }

            if (IsReplicated && String.IsNullOrEmpty(dSB[DeploymentBuildOrder.Slave_AddChangeAttribute].Query.ToString()) == false)
            {
                dSB[DeploymentBuildOrder.Master_AddChangeAttribute].Query.Append("GO\r\n");

                buildRegenerateViewsAndStoredProcedures(dSB[DeploymentBuildOrder.Slave_AddChangeAttribute].Query);
                dSB[DeploymentBuildOrder.Slave_AddChangeAttribute].Query.Append("GO\r\n");
            }
        }

        private void buildRegenerateViewsAndStoredProcedures(StringBuilder sb)
        {
            sb.Append("PRINT 'Starting [mdm].[udpRegenerateViewsAndStoredProcedures]'\r\n");
            sb.Append("EXEC [mdm].[udpRegenerateViewsAndStoredProcedures]\r\n");
        }
        private void buildAttributeSort(StringBuilder sb, ActionAttribute a)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- UPDATE Attribute: " + a.ca.DisplayName + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");

            sb.Append("UPDATE mdm.tblAttribute SET SortOrder = " + a.ca.ChangeSortOrder +
                " WHERE MUID = '" + a.ca.MUID + "'\r\n");
        }

        private void buildAttributeDelete(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionAttribute a)
        {
            if (a.caDestination != null)
            {
                buildAttributeDeleteMaster(a, dSB[DeploymentBuildOrder.Master_DeleteAttribute].Query);

                if (a.caDestination.isDomainAttribute && IsReplicated == true)
                {
                    buildAttributeDeleteDropIndexSlave(a, dSB[DeploymentBuildOrder.Slave_DeleteAttributeDomain].Query);
                }
                if (IsReplicated)
                {
                    buildAttributeDeleteSlave(a, dSB[DeploymentBuildOrder.Slave_AddChangeAttribute].Query);
                }
            }
        }

        private void buildAttributeDeleteSlave(ActionAttribute a, StringBuilder sb)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Deleting Attribute " + a.caDestination.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("ALTER TABLE [stg].[" + Server.Model + "_" + a.caDestination.Entity.Name +"_Leaf] DROP COLUMN [" + a.caDestination.Name + "]\r\n");
            sb.Append("GO\r\n");
        }

        private void buildAttributeDeleteDropIndexSlave(ActionAttribute a, StringBuilder sb)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Deleting Domain Attribute Index : " + a.caDestination.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("DECLARE @EntityTable NVARCHAR(250)\r\n");
            sb.Append("DECLARE @TableColumn NVARCHAR(250)\r\n");
            sb.Append("DECLARE @Sql NVARCHAR(1000)\r\n");
            sb.Append("SELECT @EntityTable = e.EntityTable, @TableColumn = a.TableColumn FROM mdm.tblEntity e\r\n");
            sb.Append("INNER JOIN mdm.tblAttribute a ON a.Entity_ID = e.ID\r\n");
            sb.Append("WHERE e.MUID = '" + a.caDestination.Entity.EntityMUID + "'\r\n");
            sb.Append("AND a.MUID = '" + a.caDestination.MUID + "'\r\n");
            sb.Append("\r\n");
            sb.Append("SET @Sql = 'DROP INDEX [ix_' + @EntityTable + '_Version_ID_' + @TableColumn + '] ON [mdm].[' + @EntityTable + ']'\r\n");
            sb.Append("EXEC sp_executesql @SQL\r\n");
            sb.Append("GO\r\n");
        }

        private void buildAttributeDeleteMaster(ActionAttribute a, StringBuilder sb)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Deleting Attribute: " + a.caDestination.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("EXEC [mdm].[udpAttributeDeleteByMUID] '" + a.caDestination.MUID + "'\r\n");
            sb.Append("\r\n");

            IsAttributeChanged = true;
        }

        private void buildAttributeRename(StringBuilder sb, ActionAttribute a, bool AddGo)
        {
            sb.Append("-- get UserID for: " + CurrentUser + "\r\n");
            sb.Append("DECLARE @UserID INT\r\n");
            sb.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = '" + CurrentUser + "'\r\n");
            sb.Append("-- get Input Mask Name\r\n");
            sb.Append("DECLARE @InputMaskName NVARCHAR(250)\r\n");
            sb.Append(String.Format("SELECT @InputMaskName = ListOption FROM mdm.tblList WHERE ListCode = 'lstInputMask' AND Group_ID = {0} AND OptionID = {1}\r\n", a.ca.DataTypeID, a.ca.InputMaskID));
            sb.Append("\r\n");
            sb.Append("EXEC mdm.udpAttributeSaveByMUID\r\n");
            sb.Append("@Model_MUID = NULL, @Model_Name = '" + Server.Model + "',\r\n");
            sb.Append("@Entity_MUID = NULL, @Entity_Name = '" + a.ca.Entity.Name + "',\r\n");
            sb.Append("@MemberType_ID = " + a.ca.MemberType + ",\r\n");
            sb.Append("@Attribute_MUID = '" + a.ca.MUID + "',\r\n");
            sb.Append("@Name = N'" + a.ca.Name + "',\r\n");
            sb.Append("@AttributeType_ID = " + a.ca.AttributeTypeID + ",\r\n");
            sb.Append("@DisplayWidth = " + a.ca.DisplayWidth + ",\r\n");
            sb.Append("@DomainEntity_MUID = '" + a.ca.DomainEntityMUID + "',\r\n");
            sb.Append("@DomainEntity_Name = '',\r\n");
            sb.Append("@DataType_ID = " + a.ca.DataTypeID + ",\r\n");
            sb.Append("@DataTypeInformation = " + a.ca.DataTypeInformation + ",\r\n");
            sb.Append("@InputMask_Name = @InputMaskName,\r\n");
            sb.Append("@ChangeTrackingGroup = " + a.ca.ChangeTrackingGroup + ",\r\n");

            if (Server.schemaVersion.VersionDetails.HasStagingTable)
            {
                sb.Append("@SortOrder = " + a.ca.SortOrder + ",\r\n");
            }

            sb.Append("@User_ID = @UserID\r\n");
            sb.Append("\r\n");
            if (AddGo)
            {
                sb.Append("GO\r\n");
            }

            IsAttributeChanged = true;
        }

        public void buildAttributeDifferent(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionAttribute a)
        {
            var sb = dSB[DeploymentBuildOrder.Master_AddChangeAttribute].Query;
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- UPDATE Attribute: " + a.ca.DisplayName + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");

            sb.Append("BEGIN TRANSACTION\r\n");
            sb.Append("BEGIN TRY\r\n");

            sb.Append("UPDATE mdm.tblAttribute SET MUID = '" + a.ca.MUID 
                                             + "', SortOrder = " + a.ca.SortOrder
                                             + ", ChangeTrackingGroup = " + a.ca.ChangeTrackingGroup +
                " WHERE MUID = '" + a.caDestination.MUID + "'\r\n");

            if (a.ca.MUID == a.caDestination.MUID && a.ca.Name != a.caDestination.Name)
            {
                buildAttributeRename(sb, a, false);
            }

            if(a.ca.DataTypeInformation != a.caDestination.DataTypeInformation 
                && a.ca.AttributeTypeID == a.caDestination.AttributeTypeID)
            {
                buildAttributeChangeSize(sb, a);
            }

            sb.Append("COMMIT TRANSACTION\r\n");
            sb.Append("END TRY\r\n");
            sb.Append("BEGIN CATCH\r\n");
            sb.Append("\tDECLARE\r\n");
            sb.Append("\t@ErrorMessage NVARCHAR(4000),\r\n");
            sb.Append("\t@ErrorSeverity INT,\r\n");
            sb.Append("\t@ErrorState INT;\r\n");
            sb.Append("\tEXEC mdm.udpGetErrorInfo\r\n");
            sb.Append("\t@ErrorMessage = @ErrorMessage OUTPUT,\r\n");
            sb.Append("\t@ErrorSeverity = @ErrorSeverity OUTPUT,\r\n");
            sb.Append("\t@ErrorState = @ErrorState OUTPUT;\r\n");
            sb.Append("\tROLLBACK TRANSACTION\r\n");

            sb.Append("\tPRINT 'Error has occurred:'\r\n");
            sb.Append("\tPRINT @ErrorMessage\r\n");
            sb.Append("\tPRINT 'ErrorSeverity: ' + CAST(@ErrorSeverity AS NVARCHAR(30))\r\n");
            sb.Append("\tPRINT '@ErrorState: ' + CAST(@ErrorState AS NVARCHAR(30))\r\n");
            
            sb.Append("END CATCH\r\n");
            sb.Append("GO\r\n");

            IsAttributeChanged = true;
            if (IsReplicated)
            {
                sb = dSB[DeploymentBuildOrder.Slave_AddChangeAttribute].Query;
                sb.Append("-------------------------------------------------------------------------------\r\n");
                sb.Append("-- UPDATE Attribute: " + a.ca.DisplayName + "\r\n");
                sb.Append("-------------------------------------------------------------------------------\r\n");
            }
        }

        private void buildAttributeChangeSize(StringBuilder sb, ActionAttribute a)
        {
            sb.Append("DECLARE @EntityID INT\r\n");
            sb.Append("DECLARE @ModelID INT\r\n");
            sb.Append("DECLARE @StagingBase INT\r\n");
            sb.Append("DECLARE @EntityTable NVARCHAR(100)\r\n");
            sb.Append("DECLARE @TableColumn NVARCHAR(100)\r\n");
            sb.Append("DECLARE @Sqlquery NVARCHAR(MAX)\r\n");
            sb.Append("SELECT @EntityID = ID, @ModelID = Model_ID, @EntityTable = EntityTable FROM mdm.tblEntity WHERE MUID = '" + a.caDestination.Entity.EntityMUID + "'\r\n");

            sb.Append("SELECT @TableColumn = a.TableColumn FROM mdm.tblEntity e INNER JOIN mdm.tblAttribute a ON a.Entity_ID = e.ID " +
                      "WHERE a.MUID = '" + a.caDestination.MUID + "'\r\n");

            sb.Append("IF @EntityID IS NOT NULL\r\n");
            sb.Append("BEGIN\r\n");



            if (Server.schemaVersion.VersionDetails.HasStagingTable)
            {
                if (a.ca.DataTypeID == Constants.DATA_TYPES_TEXT)
                {
                    sb.Append("\tALTER TABLE stg." + a.caDestination.Entity.StagingBase + "_Leaf ALTER COLUMN " + a.caDestination.Name + " NVARCHAR(" + a.ca.DataTypeInformation + ")\r\n");
                }
                else if(a.ca.DataTypeID == Constants.DATA_TYPES_NUMBER)
                {
                    sb.Append("\tALTER TABLE stg." + a.caDestination.Entity.StagingBase + "_Leaf ALTER COLUMN " + a.caDestination.Name + " DECIMAL(" + (38 - a.ca.DataTypeInformation) + "," + a.ca.DataTypeInformation + ")\r\n");
                }
            }

            sb.Append("\tSET @Sqlquery = 'ALTER TABLE mdm.' + @EntityTable + ' ALTER COLUMN ' + @TableColumn + ' ");
            if (a.ca.DataTypeID == Constants.DATA_TYPES_TEXT)
            {
                 sb.Append("NVARCHAR(" + a.ca.DataTypeInformation + ")");
            }
            else if (a.ca.DataTypeID == Constants.DATA_TYPES_NUMBER)
            {
                sb.Append("DECIMAL(" + (38 - a.ca.DataTypeInformation) + "," + a.ca.DataTypeInformation + ")");
            }
            sb.Append("'\r\n");
            sb.Append("\tEXEC sp_executesql @SqlQuery\r\n");
            
            sb.Append("\tUPDATE mdm.tblAttribute SET DisplayWidth = " + a.ca.DisplayWidth + ", DataTypeInformation = " + a.ca.DataTypeInformation + " WHERE MUID = '" + a.caDestination.MUID + "'\r\n");

            if (a.ca.DataTypeID != a.caDestination.DataTypeID)
            {
                sb.Append("\tUPDATE mdm.tblAttribute SET DataType_ID = " + a.ca.DataTypeID + " WHERE MUID = '" + a.caDestination.MUID + "'\r\n");    
            }

            sb.Append("\tUPDATE mdm.tblSubscriptionView SET IsDirty = 1 WHERE Entity_ID = @EntityID\r\n");
            sb.Append("\tEXEC mdm.udpCreateViews @ModelID, @EntityID\r\n");
            sb.Append("END\r\n");
            sb.Append("ELSE\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("\tPRINT 'Error Change size of attribute " + a.ca.Name + "'\r\n");
            sb.Append("\tPRINT 'Error Entity name: " + a.ca.Entity.Name + "'\r\n");
            sb.Append("\tPRINT 'Error Entity has not been found or MUID is out of sync'\r\n");
            sb.Append("END\r\n");
            sb.Append("\r\n");
            

            IsAttributeChanged = true;
        }

        private void buildAttributeMissing(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionAttribute a)
        {
            if (a.ca != null)
            {
                
                switch (a.ca.AttributeTypeID)
                {
                    case (int) mdsService.AttributeType.Domain:
                    {
                        buildAttributeDomain(dSB, a);
                    }
                        break;
                    case (int) mdsService.AttributeType.FreeForm:
                    case (int) mdsService.AttributeType.File:
                    {
                        buildAttributeFreeForm(dSB, a);
                    }
                        break;
                    default:
                        throw new NotImplementedException();

                }
            }
        }

        private void buildAttributeFreeForm(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionAttribute a)
        {
            buildAttributeFreeFormMaster(dSB[DeploymentBuildOrder.Master_AddChangeAttribute].Query, a);

            if (IsReplicated)
            {
                buildAttributeSlave(a, dSB[DeploymentBuildOrder.Slave_AddChangeAttribute].Query);
            }

        }

        private void buildAttributeFreeFormMaster(StringBuilder sb, ActionAttribute a)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Adding Freeform Attribute: " + a.ca.DisplayName + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            if (a.syncGuid == true)
            {
                sb.Append("DECLARE @Return_MUID UNIQUEIDENTIFIER\r\n");
                sb.Append("SET @Return_MUID = '" + a.ca.MUID + "'\r\n");
            }
            sb.Append("-- get UserID for: " + CurrentUser + "\r\n");
            sb.Append("DECLARE @UserID INT\r\n");
            sb.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = '" + CurrentUser + "'\r\n");
            sb.Append("-- get Input Mask Name\r\n");
            sb.Append("DECLARE @InputMaskName NVARCHAR(250)\r\n");
            sb.Append(String.Format("SELECT @InputMaskName = ListOption FROM mdm.tblList WHERE ListCode = 'lstInputMask' AND Group_ID = {0} AND OptionID = {1}\r\n", a.ca.DataTypeID, a.ca.InputMaskID));
            sb.Append("\r\n");
            sb.Append("EXEC mdm.udpAttributeSaveByMUID\r\n");
            sb.Append("@Model_MUID = NULL, @Model_Name = '" + Server.Model + "',\r\n");
            sb.Append("@Entity_MUID = NULL, @Entity_Name = '" + a.ca.Entity.Name + "',\r\n");
            sb.Append("@MemberType_ID = " + a.ca.MemberType + ",\r\n");
            sb.Append("@Attribute_MUID = NULL,\r\n");
            sb.Append("@Name = N'"+ a.ca.Name +"',\r\n");
            sb.Append("@AttributeType_ID = "+ a.ca.AttributeTypeID +",\r\n");
            sb.Append("@DisplayWidth = " + a.ca.DisplayWidth + ",\r\n");
            sb.Append("@DomainEntity_MUID = '00000000-0000-0000-0000-000000000000',\r\n");
            sb.Append("@DomainEntity_Name = DEFAULT,\r\n");
            sb.Append("@DataType_ID = " + a.ca.DataTypeID + ",\r\n");
            sb.Append("@DataTypeInformation = " + a.ca.DataTypeInformation + ",\r\n");
            sb.Append("@InputMask_Name = @InputMaskName,\r\n");
            sb.Append("@ChangeTrackingGroup = " + a.ca.ChangeTrackingGroup + ",\r\n");
            if (a.syncGuid == true)
            {
                sb.Append("@Return_MUID = @Return_MUID OUTPUT,\r\n");
            }
            if (Server.schemaVersion.VersionDetails.HasSortOrder)
            {
                sb.Append("@SortOrder = " + a.ca.SortOrder + ",\r\n");
            }
            
            sb.Append("@User_ID = @UserID\r\n");
            sb.Append("\r\n");
            sb.Append("GO\r\n");

            IsAttributeChanged = true;
        }
        private void buildAttributeDomain(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionAttribute a)
        {

            buildAttributeDomainMaster(a, dSB[DeploymentBuildOrder.Master_AddChangeAttribute].Query);

            if (IsReplicated)
            {
                buildAttributeSlave(a, dSB[DeploymentBuildOrder.Slave_AddChangeAttribute].Query);
            }
        }

        private void buildAttributeSlave(ActionAttribute a, StringBuilder sb)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Slave Adding Domain Attribute: " + a.ca.DisplayName + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");

sb.Append("DECLARE @SQL NVARCHAR(MAX)\r\n");
sb.Append("DECLARE @AttributeTypeDomain    TINYINT = 2,\r\n");
sb.Append("@AttributeTypeFile      TINYINT = 4,\r\n");
sb.Append("@AttributeType_ID INT,\r\n");
sb.Append("@EntityTable NVARCHAR(250),\r\n");
sb.Append("@DomainTable NVARCHAR(250),\r\n");
sb.Append("@TableColumn NVARCHAR(250)\r\n");
sb.Append("\r\n");
sb.Append("SELECT @AttributeType_ID = a.AttributeType_ID, @EntityTable = e.EntityTable, @DomainTable = de.EntityTable, @TableColumn = a.TableColumn  FROM mdm.tblAttribute a\r\n");
sb.Append("INNER JOIN mdm.tblEntity e ON e.ID = a.Entity_ID\r\n");
sb.Append("INNER JOIN mdm.tblModel m ON m.ID = e.Model_ID\r\n");
sb.Append("LEFT JOIN mdm.tblEntity de ON de.ID = a.DomainEntity_ID\r\n");
sb.Append("WHERE m.Name = '" + Server.Model + "'\r\n");
sb.Append("AND e.NAME = '" + a.ca.Entity.Name + "'\r\n");
sb.Append("AND a.Name = '" + a.ca.Name + "'\r\n");
sb.Append("\r\n");
sb.Append("-------------------------------------------------------------------------------\r\n");
sb.Append("--Create the FK & FK index for DBA & FIL attributes.\r\n");
sb.Append("-------------------------------------------------------------------------------\r\n");
sb.Append("IF @AttributeType_ID = @AttributeTypeDomain\r\n");
sb.Append("BEGIN\r\n");
sb.Append("    --Foreign key constraint definition\r\n");
sb.Append("    SET @SQL = N'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME =''fk_${TableName}_${DomainTable}_Version_ID_${TableColumn}'')\r\n");
sb.Append("BEGIN\r\n");
sb.Append("   ALTER TABLE mdm.${TableName} ADD CONSTRAINT fk_${TableName}_${DomainTable}_Version_ID_${TableColumn} FOREIGN KEY (Version_ID, ${TableColumn}) REFERENCES mdm.${DomainTable}(Version_ID, ID)\r\n");
sb.Append("END\r\n");
sb.Append("IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name=''ix_${TableName}_Version_ID_${TableColumn}'' AND object_id = OBJECT_ID(''mdm.${TableName}''))\r\n");
sb.Append("BEGIN\r\n");
sb.Append("   CREATE NONCLUSTERED INDEX ix_${TableName}_Version_ID_${TableColumn} ON mdm.${TableName}(Version_ID, ${TableColumn})\r\n");
sb.Append("END'\r\n");
sb.Append("\r\n");
sb.Append("	SET @SQL = REPLACE(@SQL, '${TableName}', @EntityTable)\r\n");
sb.Append("	SET @SQL = REPLACE(@SQL, '${DomainTable}', @DomainTable)\r\n");
sb.Append("	SET @SQL = REPLACE(@SQL, '${TableColumn}', @TableColumn)\r\n");
sb.Append("	EXEC sp_executesql @SQL;\r\n");
sb.Append("END\r\n");
sb.Append("ELSE IF @AttributeType_ID = @AttributeTypeFile\r\n");
sb.Append("BEGIN\r\n");
sb.Append("    --Foreign key constraint definition\r\n");
sb.Append("    SET @SQL = N'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME =''fk_${TableName}_tblFile_${TableColumn}'')\r\n");
sb.Append("BEGIN\r\n");
sb.Append("    ALTER TABLE mdm.${TableName} ADD CONSTRAINT fk_${TableName}_tblFile_${TableColumn} FOREIGN KEY (${TableColumn}) REFERENCES mdm.tblFile(ID)\r\n");
sb.Append("END\r\n");
sb.Append("IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name=''ix_${TableName}_${TableColumn}'' AND object_id = OBJECT_ID(''mdm.${TableName}''))\r\n");
sb.Append("BEGIN\r\n");
sb.Append("   CREATE NONCLUSTERED INDEX ix_${TableName}_${TableColumn} ON mdm.${TableName}(${TableColumn})\r\n");
sb.Append("END'\r\n");
sb.Append("	SET @SQL = REPLACE(@SQL, '${TableName}', @EntityTable)\r\n");
sb.Append("	SET @SQL = REPLACE(@SQL, '${TableColumn}', @TableColumn)\r\n");
sb.Append("\r\n");
sb.Append("    EXEC sp_executesql @SQL;\r\n");
sb.Append("\r\n");
sb.Append("END; --if\r\n");
sb.Append("GO\r\n");
sb.Append("-------------------------------------------------------------------------------\r\n");
sb.Append("-- Staging Table\r\n");
sb.Append("-------------------------------------------------------------------------------\r\n");
sb.Append("DECLARE @SQL NVARCHAR(MAX)\r\n");
sb.Append(" -- MemberType constants\r\n");
sb.Append("DECLARE @MemberTypeLeaf         TINYINT = 1,\r\n");
sb.Append("@MemberTypeConsolidated TINYINT = 2,\r\n");
sb.Append("@MemberTypeCollection   TINYINT = 3,\r\n");
sb.Append("-- AttributeType constants\r\n");
sb.Append("@AttributeTypeFreeform  TINYINT = 1,\r\n");
sb.Append("@AttributeTypeDomain    TINYINT = 2,\r\n");
sb.Append("@AttributeTypeSystem    TINYINT = 3,\r\n");
sb.Append("@AttributeTypeFile      TINYINT = 4,\r\n");
sb.Append("-- AttributeDataType constants\r\n");
sb.Append("@DataTypeText           TINYINT = 1,\r\n");
sb.Append("@DataTypeNumber         TINYINT = 2,\r\n");
sb.Append("@DataTypeDateTime       TINYINT = 3,\r\n");
sb.Append("@DataTypeLink           TINYINT = 6,\r\n");
sb.Append("\r\n");
sb.Append("\r\n");
sb.Append("@StagingTableName NVARCHAR(MAX) = '" + a.ca.Entity.StagingBase +"_Leaf',\r\n");
sb.Append("@Name NVARCHAR(MAX) = '" + a.ca.Name + "',\r\n");
sb.Append("@AttributeType_ID INT,\r\n");
sb.Append("@DataType_ID INT,\r\n");
sb.Append("@DataTypeInformation INT\r\n");
sb.Append("\r\n");
sb.Append("SELECT @AttributeType_ID = a.AttributeType_ID, @DataType_ID = a.DataType_ID, @DataTypeInformation = a.DataTypeInformation\r\n");
sb.Append("FROM mdm.tblAttribute a\r\n");
sb.Append("INNER JOIN mdm.tblEntity e ON e.ID = a.Entity_ID\r\n");
sb.Append("INNER JOIN mdm.tblModel m ON m.ID = e.Model_ID\r\n");
sb.Append("WHERE m.Name = '" + Server.Model + "'\r\n");
sb.Append("AND e.NAME = '" + a.ca.Entity.Name + "'\r\n");
sb.Append("AND a.Name = '" + a.ca.Name + "'\r\n");
sb.Append("\r\n");
sb.Append("IF LEN(COALESCE(@StagingTableName, N'')) > 0\r\n");
sb.Append("BEGIN\r\n");
sb.Append("    SET @SQL = N'ALTER TABLE stg.' + quotename(@StagingTableName) + N' ADD ' + quotename(@Name) + N' ';\r\n");
sb.Append("    IF @AttributeType_ID = @AttributeTypeFreeform BEGIN\r\n");
sb.Append("        IF @DataType_ID = @DataTypeText OR @DataType_ID = @DataTypeLink BEGIN\r\n");
sb.Append("            IF @DataTypeInformation < 1 SET @DataTypeInformation = 1;\r\n");
sb.Append("            ELSE IF @DataTypeInformation > 4000 SET @DataTypeInformation = 4000; --Arbitrary limit\r\n");
sb.Append("            SET @SQL = @SQL + N'NVARCHAR(' + CONVERT(NVARCHAR(30), @DataTypeInformation) + N') NULL;';\r\n");
sb.Append("        END ELSE IF @DataType_ID = @DataTypeNumber BEGIN\r\n");
sb.Append("            IF @DataTypeInformation < 0 SET @DataTypeInformation = 0; --DECIMAL(38, 0) is minimum precision allowed by SQL\r\n");
sb.Append("            ELSE IF @DataTypeInformation > 38 SET @DataTypeInformation = 38; --DECIMAL(38, 38) is maximum precision allowed by SQL\r\n");
sb.Append("            SET @SQL = @SQL + N'DECIMAL(38, ' + CONVERT(NVARCHAR(2), @DataTypeInformation) + N') NULL;';\r\n");
sb.Append("        END ELSE IF @DataType_ID = @DataTypeDateTime BEGIN\r\n");
sb.Append("            SET @SQL = @SQL + N'DATETIME2(3) NULL;';\r\n");
sb.Append("        END; --if\r\n");
sb.Append("    END ELSE BEGIN --DBA/FILE\r\n");
sb.Append("        SET @SQL = @SQL + N'NVARCHAR(250) NULL;';\r\n");
sb.Append("    END --if \r\n");
sb.Append("    EXEC sp_executesql @SQL;\r\n");
sb.Append("\r\n");
sb.Append("END; --IF\r\n");
sb.Append("GO\r\n");
        }

        private void buildAttributeDomainMaster(ActionAttribute a, StringBuilder sb)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Master Adding Domain Attribute: " + a.ca.DisplayName + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            if (a.syncGuid == true)
            {
                sb.Append("DECLARE @Return_MUID UNIQUEIDENTIFIER\r\n");
                sb.Append("SET @Return_MUID = '" + a.ca.MUID + "'\r\n");
            }
            sb.Append("-- get UserID for: " + CurrentUser + "\r\n");
            sb.Append("DECLARE @UserID INT\r\n");
            sb.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = '" + CurrentUser + "'\r\n");

            sb.Append("DECLARE @DomainEntityName NVARCHAR(250)\r\n");
            sb.Append("SELECT @DomainEntityName = de.Name FROM mdm.tblEntity de\r\n" +
                      "INNER JOIN mdm.tblModel m ON m.ID = de.Model_ID\r\n" +
                      "WHERE de.MUID = '" + a.ca.DomainEntityMUID + "' AND m.Name = '" + Server.Model + "'\r\n");
            sb.Append("\r\n");
            sb.Append("IF @DomainEntityName IS NOT NULL\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("exec mdm.udpAttributeSaveByMUID\r\n");
            sb.Append("@Model_MUID=NULL,\r\n");
            sb.Append("@Model_Name= '" + Server.Model + "',\r\n");
            sb.Append("@Entity_MUID=NULL,\r\n");
            sb.Append("@Entity_Name= '" + a.ca.Entity.Name + "',\r\n");
            sb.Append("@MemberType_ID= " + a.ca.MemberType + ",\r\n");
            sb.Append("@Attribute_MUID=NULL,\r\n");
            sb.Append("@Name=N'" + a.ca.Name + "',\r\n");
            sb.Append("@AttributeType_ID = " + a.ca.AttributeTypeID + ",\r\n");
            sb.Append("@DisplayWidth = " + a.ca.DisplayWidth + ",\r\n");
            sb.Append("@DomainEntity_MUID=NULL,\r\n");
            sb.Append("@DomainEntity_Name= @DomainEntityName,\r\n");
            sb.Append("@DataType_ID = " + a.ca.DataTypeID + ",\r\n");
            sb.Append("@DataTypeInformation = " + a.ca.DataTypeInformation + ",\r\n");
            sb.Append("@InputMask_Name=NULL,\r\n");
            sb.Append("@ChangeTrackingGroup=" + a.ca.ChangeTrackingGroup + ",\r\n");
            if (a.syncGuid == true)
            {
                sb.Append("@Return_MUID = @Return_MUID OUTPUT,\r\n");
            }
            if (Server.schemaVersion.VersionDetails.HasSortOrder)
            {
                sb.Append("@SortOrder = " + a.ca.SortOrder + ",\r\n");
            }

            sb.Append("@User_ID = @UserID\r\n");
            sb.Append("END\r\n");
            sb.Append("ELSE\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("\tPRINT 'Error adding domain attribute " + a.ca.Name + "'\r\n");
            sb.Append("\tPRINT 'Error Entity name: " + a.ca.Entity.Name + "'\r\n");
            sb.Append("\tPRINT 'Error Entity has not been created or MUID is out of sync'\r\n");
            sb.Append("END\r\n");
            sb.Append("\r\n");
            sb.Append("GO\r\n");

            IsAttributeChanged = true;
        }

        #endregion
        
        #region AttributeGroups
        private void buildAttributeGroups(StringBuilder sb)
        {
            foreach (var ag in LActionAttributeGroups)
            {
                switch (ag.status)
                {
                    case ObjectStatus.Missing:
                        {
                            buildAttributeGroupMissing(sb, ag);
                        }
                        break;
                    case ObjectStatus.Different:
                        {
                            buildAttributeGroupDifferent(sb, ag);
                        }
                        break;
                    case ObjectStatus.Rename:
                        {
                            buildAttributeGroupRename(sb, ag);
                        }
                        break;
                    case ObjectStatus.Delete:
                        {
                            buildAttributeGroupDelete(sb, ag);
                        }
                        break;
                    default:
                        throw new NotImplementedException();
                }
            }
        }

        private void buildAttributeGroupMissing(StringBuilder sb, ActionAttributeGroup ag)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Create Attribute Group: " + ag.cag.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("\r\n");
            sb.Append("\r\n");
            if (ag.syncGuid == true)
            {
                sb.Append("DECLARE @Return_MUID UNIQUEIDENTIFIER\r\n");
                sb.Append("DECLARE @Return_ID INT\r\n");
                sb.Append("SET @Return_MUID = '" + ag.cag.MUID + "'\r\n");
            }
            sb.Append("-- get UserID for: " + CurrentUser + "\r\n");
            sb.Append("DECLARE @UserID INT\r\n");
            sb.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = '" + CurrentUser + "'\r\n");
            sb.Append("EXEC [mdm].[udpAttributeGroupSaveByMUID] \r\n");
            sb.Append("@User_ID = @UserID\r\n");
            sb.Append(",@Model_MUID = NULL\r\n");
            sb.Append(",@Model_Name = '" + Server.Model + "'\r\n");
            sb.Append(",@Entity_MUID = '" + ag.cag.Entity.EntityMUID + "'\r\n");
            sb.Append(",@Entity_Name = '" + ag.cag.Entity.Name + "'\r\n");
            sb.Append(",@MemberType_ID = 1\r\n");
            sb.Append(",@MUID = NULL\r\n");
            sb.Append(",@Name = '" + ag.cag.Name + "'\r\n");
            sb.Append(",@SortOrder = " + ag.cag.SortOrder + "\r\n");

            if (ag.syncGuid == true)
            {
                sb.Append(",@Return_MUID = @Return_MUID OUTPUT\r\n");
                sb.Append(",@Return_ID = @Return_ID OUTPUT\r\n");
            }
            sb.Append("GO\r\n");
        }

        private void buildAttributeGroupDifferent(StringBuilder sb, ActionAttributeGroup ag)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- UPDATE Attribute Group: " + ag.cag.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("UPDATE mdm.tblAttributeGroup SET MUID = '" + ag.cag.MUID + "', SortOrder = " + ag.cag.SortOrder + " WHERE MUID = '" + ag.cagDestination.MUID + "'\r\n");
        }

        private void buildAttributeGroupRename(StringBuilder sb, ActionAttributeGroup ag)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Rename Attribute Group: " + ag.cag.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("\r\n");
            sb.Append("-- get UserID for: " + CurrentUser + "\r\n");
            sb.Append("DECLARE @UserID INT\r\n");
            sb.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = '" + CurrentUser + "'\r\n");
            sb.Append("EXEC [mdm].[udpAttributeGroupSaveByMUID] \r\n");
            sb.Append("@User_ID = @UserID\r\n");
            sb.Append(",@Model_MUID = NULL\r\n");
            sb.Append(",@Model_Name = '" + Server.Model + "'\r\n");
            sb.Append(",@Entity_MUID = '" + ag.cag.Entity.EntityMUID + "'\r\n");
            sb.Append(",@Entity_Name = '" + ag.cag.Entity.Name + "'\r\n");
            sb.Append(",@MemberType_ID = 1\r\n");
            sb.Append(",@MUID = '" + ag.cag.MUID + "'\r\n");
            sb.Append(",@Name = '" + ag.cag.Name +"'\r\n");
            sb.Append(",@SortOrder = " + ag.cag.SortOrder + "\r\n");
            sb.Append("GO\r\n");
        }

        private void buildAttributeGroupDelete(StringBuilder sb, ActionAttributeGroup ag)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- DELETE Attribute Group: " + ag.cagDestination.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("EXEC [mdm].[udpAttributeGroupDeleteByMUID] @MUID = '" + ag.cagDestination.MUID + "'\r\n");
        }
        #endregion

        #region AttributeGroupDetails
        private void buildAttributeGroupDetails(StringBuilder sb)
        {
            foreach (var agd in LActionAttributeGroupDetails)
            {
                switch (agd.status)
                {
                    case ObjectStatus.Missing:
                        {
                            buildAttributeGroupDetailMissing(sb, agd);
                        }
                        break;
                    case ObjectStatus.Different:
                        {
                            buildAttributeGroupDetailDifferent(sb, agd);
                        }
                        break;
                    case ObjectStatus.Rename:
                        {
                            throw new Exception("Not possible to rename Attribute Group Detail as there is no name");
                        }
                    case ObjectStatus.Delete:
                        {
                            buildAttributeGroupDetailDelete(sb, agd);
                        }
                        break;
                    default:
                        throw new NotImplementedException();
                }
            }   
        }

        private void buildAttributeGroupDetailDelete(StringBuilder sb, ActionAttributeGroupDetail agd)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Delete Attribute Group Detail : " + agd.cagdDestination.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("\r\n");
            //for some reason MDS deletes the all the AGDs associated to the AG when calling udpAttributeGroupDelete. Then rebuilds all the AGDs
            sb.Append("DELETE FROM mdm.tblAttributeGroupDetail WHERE MUID = '" + agd.cagdDestination.MUID + "'\r\n");
            sb.Append("\r\n");
            sb.Append("GO\r\n");
        }

        private void buildAttributeGroupDetailDifferent(StringBuilder sb, ActionAttributeGroupDetail agd)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- UPDATE Attribute Group Detail : " + agd.cagd.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("\r\n");
            //for some reason MDS deletes the all the AGDs associated to the AG when calling udpAttributeGroupDelete. Then rebuilds all the AGDs
            sb.Append("UPDATE mdm.tblAttributeGroupDetail SET MUID = '" + agd.cagd.MUID + "', SortOrder = " + agd.cagd.SortOrder + " WHERE MUID = '" + agd.cagdDestination.MUID + "'\r\n");
            sb.Append("\r\n");
            sb.Append("GO\r\n");
        }

        private void buildAttributeGroupDetailMissing(StringBuilder sb, ActionAttributeGroupDetail agd)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Create Attribute Group Detail : " + agd.cagd.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("\r\n");
            sb.Append("DECLARE @Return_ID INT\r\n");
            sb.Append("DECLARE @UserID INT\r\n");
            sb.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = '" + CurrentUser + "'\r\n");
            sb.Append("EXEC [mdm].[udpAttributeGroupDetailSaveByMUID]\r\n");
            sb.Append("@User_ID = @UserID,\r\n");
            sb.Append("@AttributeGroup_MUID = '" + agd.cagd.AttributeGroup.MUID + "',\r\n");
            sb.Append("@MUID = '" + agd.cagd.Attribute.MUID + "',\r\n");
            sb.Append("@Name = '" + agd.cagd.Attribute.Name + "',\r\n");
            sb.Append("@Type_ID = 1\r\n");
            
            if (agd.syncGuid == true)
            {
                //sb.Append(",@Return_MUID = @Return_MUID OUTPUT\r\n"); This not used in the SP currently
                sb.Append(",@Return_ID = @Return_ID OUTPUT\r\n");
                sb.Append("\r\n");
                sb.Append("UPDATE mdm.tblAttributeGroupDetail SET MUID = '" + agd.cagd.MUID + "', SortOrder = " + agd.cagd.SortOrder + " WHERE ID  = @Return_ID\r\n");

            }
            sb.Append("\r\n");
            sb.Append("GO\r\n");
        }
        #endregion

        #region Entities
        private void buildEntities(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB)
        {
            foreach (var ae in LActionEntities)
            {
                switch (ae.status)
                {
                    case ObjectStatus.Different:
                        {
                            buildEntityDifferent(dSB, ae);
                        }
                        break;
                    case ObjectStatus.Missing:
                        {
                            buildEntityCreate(dSB, ae);
                            break;
                        }
                        
                    case ObjectStatus.Rename:
                        {
                            buildEntityRename(dSB, ae);
                            break;
                        }
                    case ObjectStatus.Delete:
                        {
                            buildEntityDelete(dSB, ae);
                            break;
                        }
                    
                    default:
                        throw new NotImplementedException();
                }
            }   
        }

        private void buildEntityRename(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionEntity ae)
        {
            var sb = dSB[DeploymentBuildOrder.Master_ChangeEntity].Query;
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Rename Entity : " + ae.ceDestination.Name + " to " +  ae.ce.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("\r\n");
            sb.Append("-- get UserID for: " + CurrentUser + "\r\n");
            sb.Append("DECLARE @UserID INT\r\n");
            sb.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = '" + CurrentUser + "'\r\n");
            sb.Append("EXEC [mdm].[udpEntitySaveByMUID]\r\n");
            sb.Append("@User_ID = @UserID\r\n");
            sb.Append(",@Model_MUID = NULL\r\n");
            sb.Append(",@Model_Name = '" + Server.Model + "'\r\n");
            sb.Append(",@Entity_MUID = '" + ae.ce.EntityMUID + "'\r\n");
            sb.Append(",@Entity_Name = '" + ae.ce.Name + "'\r\n");
            if (Server.schemaVersion.VersionDetails.HasCodeGenSeed)
            {
                //Sending a null will set it to 0
                if (ae.ce.CodeGenSeed == 1)
                {
                    sb.Append(",@CodeGenSeed = " + ae.ce.CodeGenSeed + "\r\n");
                }
            }
            if (Server.schemaVersion.VersionDetails.HasStagingTable)
            {
                if (Server.Model + "_" + ae.ce.Name == ae.ce.StagingBase)
                {
                    sb.Append(",@StagingBase = '" + ae.ceDestination.StagingBase + "'\r\n");
                }
                
            }
            sb.Append("\r\n");
            sb.Append("GO\r\n");

            buildEntityStagingTable(dSB, ae);
        }

        private void buildEntityDifferent(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionEntity ae)
        {
            var sb = dSB[DeploymentBuildOrder.Master_ChangeEntity].Query;
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- UPDATE Entity: " + ae.ce.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");

            sb.Append("UPDATE mdm.tblEntity SET MUID = '" + ae.ce.EntityMUID + "' WHERE MUID = '" + ae.ceDestination.EntityMUID + "'\r\n");

            buildEntityCodeGen(dSB, ae);
            buildEntityStagingTable(dSB, ae);


        }
        private void buildEntityStagingTable(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionEntity ae)
        {
            if (Server.schemaVersion.VersionDetails.HasStagingTable && (ae.ce.StagingBase != ae.ceDestination.StagingBase))
            {
                if (ae.buildStaging == true)
                {
                    buildEntityStagingTableReplicated(ae, dSB[DeploymentBuildOrder.Master_ChangeEntity].Query, true);
                    if (IsReplicated)
                    {
                        buildEntityStagingTableReplicated(ae, dSB[DeploymentBuildOrder.Slave_ChangeEntity].Query, true);
                    }
                }
            }
        }

        private static void buildEntityStagingTableReplicated(ActionEntity ae, StringBuilder sb, bool isMaster)
        {
            sb.Append("DECLARE @EntityID INT\r\n");

            sb.Append("SELECT @EntityID = ID FROM mdm.tblEntity WHERE MUID = '" + ae.ce.EntityMUID + "'\r\n");
            sb.Append("IF @EntityID IS NOT NULL\r\n");
            sb.Append("BEGIN\r\n");

            string newStaging = ae.ce.StagingBase;
            string oldStaging = ae.ceDestination.StagingBase;

            if (isMaster)
            {
                sb.Append("UPDATE mdm.tblEntity SET StagingBase = '" + newStaging + "' WHERE ID = @EntityID\r\n");
            }
            sb.Append("DROP PROC stg.udp_" + oldStaging + "_Leaf\r\n");
            sb.Append("DROP VIEW stg.viw_" + oldStaging + "_MemberErrorDetails\r\n");
            sb.Append("EXEC sp_rename 'stg." + oldStaging + "_Leaf', '" + newStaging + "_Leaf'\r\n");
            sb.Append("EXEC sp_rename 'stg." + newStaging + "_Leaf.pk_" + oldStaging + "_Leaf', 'pk_" + newStaging + "_Leaf'\r\n");
            sb.Append("EXEC sp_rename 'stg.ck_" + oldStaging + "_Leaf_ImportType', 'ck_" + newStaging + "_Leaf_ImportType'\r\n");
            sb.Append("EXEC sp_rename 'stg.ck_" + oldStaging + "_Leaf_ImportStatus_ID', 'ck_" + newStaging + "_Leaf_ImportStatus_ID'\r\n");
            sb.Append("EXEC sp_rename 'stg." + newStaging + "_Leaf.ix_" + oldStaging + "_Leaf_Batch_ID', 'ix_" + newStaging + "_Leaf_Batch_ID'\r\n");
            sb.Append("EXEC sp_rename 'stg." + newStaging + "_Leaf.ix_" + oldStaging + "_Leaf_BatchTag', 'ix_" + newStaging + "_Leaf_BatchTag'\r\n");
            sb.Append("EXEC sp_rename 'stg.df_" + oldStaging + "_Leaf_ImportStatus_ID', 'df_" + newStaging + "_Leaf_ImportStatus_ID'\r\n");
            sb.Append("EXEC mdm.udpEntityStagingCreateLeafStoredProcedure @EntityID\r\n");
            sb.Append("EXEC mdm.udpCreateEntityStagingErrorDetailViews @EntityID\r\n");
            sb.Append("END\r\n");
            sb.Append("ELSE\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("\tPRINT 'Error Updating entity'\r\n");
            sb.Append("\tPRINT 'Error " + ae.ce.Name + "'\r\n");
            sb.Append("\tPRINT 'Error Entity has not been found or MUID is out of sync'\r\n");
            sb.Append("END\r\n");
            sb.Append("\r\n");
            sb.Append("GO\r\n");
        }

        private void buildEntityCodeGen(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionEntity ae)
        {
            if (ae.ce.CodeGenSeed != ae.ceDestination.CodeGenSeed)
            {
                var sb = dSB[DeploymentBuildOrder.Master_ChangeEntity].Query;
                sb.Append("-------------------------------------------------------------------------------\r\n");
                sb.Append("-- Update Entity : " + ae.ce.Name + "\r\n");
                sb.Append("-------------------------------------------------------------------------------\r\n");
                sb.Append("\r\n");
                sb.Append("-- get UserID for: " + CurrentUser + "\r\n");
                sb.Append("DECLARE @UserID INT\r\n");
                sb.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = '" + CurrentUser + "'\r\n");

                sb.Append("DECLARE @ModelMUID UNIQUEIDENTIFIER \r\n");
                sb.Append("SELECT @ModelMUID = MUID FROM mdm.tblModel WHERE Name = '" + Server.Model + "'\r\n");
                sb.Append("IF @ModelMUID IS NOT NULL\r\n");
                sb.Append("BEGIN\r\n");
                sb.Append("EXEC [mdm].[udpEntitySaveByMUID]\r\n");
                sb.Append("@User_ID = @UserID\r\n");
                sb.Append(",@Model_MUID = @ModelMUID\r\n");
                sb.Append(",@Model_Name = '" + Server.Model + "'\r\n");
                sb.Append(",@Entity_MUID = '" + ae.ce.EntityMUID + "'\r\n");
                sb.Append(",@Entity_Name = '" + ae.ce.Name + "'\r\n");
                if (Server.schemaVersion.VersionDetails.HasStagingTable)
                {
                    sb.Append(",@StagingBase = '" + ae.ce.StagingBase + "'\r\n");

                    string codeGenSeed = (ae.ce.CodeGenSeed == 0) ? "NULL" : "1";
                    sb.Append(",@CodeGenSeed = " + codeGenSeed + "\r\n");
                }
                sb.Append("END\r\n");
                sb.Append("ELSE\r\n");
                sb.Append("BEGIN\r\n");
                sb.Append("\tPRINT 'Error Updating entity'\r\n");
                sb.Append("\tPRINT 'Error " + ae.ce.Name + "'\r\n");
                sb.Append("\tPRINT 'Error Entity has not been found or MUID is out of sync'\r\n");
                sb.Append("END\r\n");
                sb.Append("\r\n");
                sb.Append("GO\r\n");
            }
        }
        #region Delete Entity
        private void buildEntityDelete(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionEntity ae)
        {
            if (IsReplicated)
            {
                buildEntityDeleteArticle(ae, dSB[DeploymentBuildOrder.Master_DeleteArticle].Query);
                
                buildEntityDeleteArticle(ae, dSB[DeploymentBuildOrder.Slave_DeleteArticle].Query);
                buildEntityDeleteSlave(ae, dSB[DeploymentBuildOrder.Slave_DeleteEntity].Query);
            }

            buildEntityDeleteMaster(ae, dSB[DeploymentBuildOrder.Master_DeleteEntity].Query);
            
        }

        private void buildEntityDeleteSlave(ActionEntity ae, StringBuilder sb)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Slave Delete Entity: " + ae.ceDestination.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("DECLARE @SqlQuery NVARCHAR(MAX)\r\n");
            sb.Append("DECLARE @EntityID INT\r\n");
            sb.Append("DECLARE @ModelID INT\r\n");
            sb.Append("DECLARE @EntityTable NVARCHAR(100)\r\n");
            sb.Append("DECLARE @SecurityTable NVARCHAR(100)\r\n");
            sb.Append("DECLARE @StagingBase NVARCHAR(100)\r\n");
            sb.Append("SELECT @ModelID = m.ID, @EntityID = e.ID, @EntityTable = e.EntityTable, @SecurityTable = e.SecurityTable, @StagingBase = e.StagingBase FROM mdm.tblModel m INNER JOIN mdm.tblEntity e ON e.Model_ID = m.ID WHERE e.MUID = '" + ae.ceDestination.EntityMUID + "'\r\n");
            sb.Append("\r\n");
            sb.Append("IF EXISTS(SELECT 1 FROM mdm.tblAttribute WHERE DomainEntity_ID = @EntityID)\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("	PRINT 'Error - can not delete entity: " + ae.ceDestination.Name + " as it is used as a domain attribute'\r\n");
            sb.Append("END\r\n");
            sb.Append("ELSE\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("SET @SqlQuery = N'\r\n");
            sb.Append("IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(''stg.viw_' +  @StagingBase + '_MemberErrorDetails''))\r\n");
            sb.Append("   DROP VIEW stg.viw_' +  @StagingBase + '_MemberErrorDetails\r\n");
            sb.Append("IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(''stg.viw_' +  @StagingBase + '_RelationshipErrorDetails''))\r\n");
            sb.Append("   DROP VIEW stg.viw_' +  @StagingBase + '_RelationshipErrorDetails\r\n");
            sb.Append("IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(''mdm.' +  @StagingBase + '''))\r\n");
            sb.Append("   DROP VIEW mdm.' +  @StagingBase + '\r\n");
            sb.Append("IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(''mdm.viw_SYSTEM_' + CAST(@ModelID AS NVARCHAR(10)) + '_' + CAST(@EntityID AS NVARCHAR(10)) + '_CHILDATTRIBUTES''))\r\n");
            sb.Append("   DROP VIEW mdm.viw_SYSTEM_' + CAST(@ModelID AS NVARCHAR(10)) + '_' + CAST(@EntityID AS NVARCHAR(10)) + '_CHILDATTRIBUTES\r\n");
            sb.Append("IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(''mdm.viw_SYSTEM_' + CAST(@ModelID AS NVARCHAR(10)) + '_' + CAST(@EntityID AS NVARCHAR(10)) + '_CHILDATTRIBUTES_EXP''))\r\n");
            sb.Append("   DROP VIEW mdm.viw_SYSTEM_' + CAST(@ModelID AS NVARCHAR(10)) + '_' + CAST(@EntityID AS NVARCHAR(10)) + '_CHILDATTRIBUTES_EXP\r\n");
            sb.Append("IF OBJECT_ID(''stg.udp_' +  @StagingBase + '_Leaf'', ''P'') IS NOT NULL\r\n");
            sb.Append("   DROP PROC stg.udp_' +  @StagingBase + '_Leaf\r\n");
            sb.Append("IF EXISTS(SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(''stg.' +  @StagingBase + '_Leaf''))\r\n");
            sb.Append("   DROP TABLE stg.' +  @StagingBase + '_Leaf\r\n");
            sb.Append("IF EXISTS(SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(''mdm.' + @SecurityTable + '''))\r\n");
            sb.Append("   DROP TABLE mdm.' + @SecurityTable + '\r\n");
            sb.Append("IF EXISTS(SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(''mdm.' + @EntityTable + '''))\r\n");
            sb.Append("   DROP TABLE mdm.' + @EntityTable\r\n");
            sb.Append("EXEC sp_executesql @SqlQuery\r\n");
            sb.Append("END\r\n");
            sb.Append("\r\n");
            sb.Append("GO\r\n");
        }

        private void buildEntityDeleteMaster(ActionEntity ae, StringBuilder sb)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Delete Entity : " + ae.ceDestination.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("\r\n");
            sb.Append("DECLARE @EntityID INT \r\n");
            sb.Append("SELECT @EntityID = e.ID FROM mdm.tblModel m INNER JOIN mdm.tblEntity e ON e.Model_ID = m.ID WHERE e.MUID = '" + ae.ceDestination.EntityMUID + "'\r\n");
            sb.Append("\r\n");
            sb.Append("IF EXISTS(SELECT 1 FROM mdm.tblAttribute WHERE DomainEntity_ID = @EntityID)\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("	PRINT 'Error - can not delete entity: " + ae.ceDestination.Name + " as it is used as a domain attribute'\r\n");
            sb.Append("END\r\n");
            sb.Append("ELSE\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("	EXEC [mdm].[udpEntityDelete] @Entity_ID = @EntityID\r\n");
            sb.Append("END\r\n");
            sb.Append("\r\n");
            sb.Append("GO\r\n");
        }
        private void buildEntityDeleteArticle(ActionEntity ae, StringBuilder sb)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Drop Replicated Article Entity: " + ae.ceDestination.Name + ".\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("DECLARE @EntityID INT \r\n");
            sb.Append("SELECT @EntityID = e.ID FROM mdm.tblModel m INNER JOIN mdm.tblEntity e ON e.Model_ID = m.ID WHERE e.MUID = '" + ae.ceDestination.EntityMUID + "'\r\n");
            sb.Append("\r\n");
            sb.Append("IF EXISTS(SELECT 1 FROM mdm.tblAttribute WHERE DomainEntity_ID = @EntityID)\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("	PRINT 'Error - can not delete entity: " + ae.ceDestination.Name + " as it is used as a domain attribute'\r\n");
            sb.Append("END\r\n");
            sb.Append("ELSE\r\n");
            sb.Append("BEGIN\r\n");
            ReplicationDropArticle(sb, ae);
            sb.Append("END\r\n");
            sb.Append("\r\n");
            sb.Append("GO\r\n");
        }
        #endregion
        #region Create Entity
        private void buildEntityCreate(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionEntity ae)
        {
            buildEntityCreateMaster(ae, dSB[DeploymentBuildOrder.Master_AddEntity].Query);

            if (IsReplicated)
            {
                buildReplicationJob(dSB[DeploymentBuildOrder.Master_JobStop].Query, false, Server.Database, dSB[DeploymentBuildOrder.Master_JobStop].IsMaster);
                buildReplicationJob(dSB[DeploymentBuildOrder.Slave_JobStop].Query, false, Server.Database, dSB[DeploymentBuildOrder.Slave_JobStop].IsMaster);

                buildEntityCreateSlave(ae, dSB[DeploymentBuildOrder.Slave_AddEntity].Query);

                buildEntityAddArticle(ae, dSB[DeploymentBuildOrder.Master_AddArticle].Query, dSB[DeploymentBuildOrder.Master_AddArticle].IsMaster);

                buildEntityAddArticle(ae, dSB[DeploymentBuildOrder.Slave_AddArticle].Query, dSB[DeploymentBuildOrder.Slave_AddArticle].IsMaster);

                buildReplicationJob(dSB[DeploymentBuildOrder.Master_JobStart].Query, true, Server.Database, dSB[DeploymentBuildOrder.Master_JobStart].IsMaster);
                buildReplicationJob(dSB[DeploymentBuildOrder.Slave_JobStart].Query, true, Server.Database, dSB[DeploymentBuildOrder.Slave_JobStart].IsMaster);
            }
        }

        private void buildReplicationJob(StringBuilder sb, bool jobStatus, string databaseName, bool isMaster)
        {
            //want to prevent stopping and starting the job multiple times per entity
            if (sb.Length == 0)
            {
                if (jobStatus == false && isMaster == true)
                {
                    ReplicationCheckRequestResponse(sb);
                    ReplicationJob(sb, jobStatus, databaseName);
                }
                else
                {
                    ReplicationJob(sb, jobStatus, databaseName);
                }
            }
        }
        private void buildEntityAddArticle(ActionEntity ae, StringBuilder sb, bool isMaster)
        {
            if (IsAddMasterAtricle == false && isMaster == true)
            {
                ReplicationAddArticle(sb);
                IsAddMasterAtricle = true;
            }

            if (!IsAddSlaveAtricle && isMaster == false)
            {
                ReplicationAddArticle(sb);
                IsAddSlaveAtricle = true;
            }
        }
        private void buildEntityCreateSlave(ActionEntity ae, StringBuilder sb)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Create Slave Entity objects: " + ae.ce.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("DECLARE @SQL NVARCHAR(MAX)\r\n");
            sb.Append("DECLARE @EntityTable NVARCHAR(MAX)\r\n");
            sb.Append("DECLARE @StagingTable NVARCHAR(MAX)\r\n");
            sb.Append("DECLARE @SecurityTable NVARCHAR(MAX)\r\n");
            sb.Append("DECLARE @Model_ID INT\r\n");
            sb.Append("DECLARE @Entity_ID INT\r\n");
            sb.Append("\r\n");
            sb.Append("SELECT @Model_ID = m.ID,  @Entity_ID = e.ID, @EntityTable = e.EntityTable, @StagingTable = e.StagingBase + N'_Leaf', @SecurityTable = e.SecurityTable FROM mdm.tblModel m\r\n");
            sb.Append("INNER JOIN mdm.tblEntity e ON e.Model_ID = m.ID\r\n");
            sb.Append("WHERE m.Name = '" + ae.ce.Model.Name + "'\r\n");
            sb.Append("AND e.Name = '" + ae.ce.Name + "'\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- logic pulled from [mdm].[udpEntitySave]\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Entity Table\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("SET @SQL = \r\n");
            sb.Append("'CREATE TABLE mdm.{EntityTable}\r\n");
            sb.Append("(\r\n");
            sb.Append("	Version_ID          INT NOT NULL,\r\n");
            sb.Append("	ID                  INT IDENTITY (1, 1) NOT NULL,\r\n");
            sb.Append("	Status_ID           TINYINT NOT NULL CONSTRAINT df_{EntityTable}_Status_ID DEFAULT 1,\r\n");
            sb.Append("	ValidationStatus_ID TINYINT NOT NULL CONSTRAINT df_{EntityTable}_ValidationStatus_ID DEFAULT 0,\r\n");
            sb.Append("	[Name]              NVARCHAR(250) NULL,\r\n");
            sb.Append("	Code                NVARCHAR(250) NOT NULL,\r\n");
            sb.Append("	ChangeTrackingMask  INT NOT NULL CONSTRAINT df_{EntityTable}_ChangeTrackingMask DEFAULT 0,\r\n");
            sb.Append("	EnterDTM            DATETIME2(3) NOT NULL CONSTRAINT df_{EntityTable}_EnterDTM DEFAULT GETUTCDATE(),\r\n");
            sb.Append("	EnterUserID         INT NOT NULL,\r\n");
            sb.Append("	EnterVersionID      INT NOT NULL,\r\n");
            sb.Append("	LastChgDTM          DATETIME2(3) NOT NULL CONSTRAINT df_{EntityTable}_LastChgDTM DEFAULT GETUTCDATE(),\r\n");
            sb.Append("	LastChgUserID       INT NOT NULL,\r\n");
            sb.Append("	LastChgVersionID    INT NOT NULL,\r\n");
            sb.Append("	AsOf_ID             INT NULL,\r\n");
            sb.Append("	MUID                UNIQUEIDENTIFIER NOT NULL CONSTRAINT df_{EntityTable}_MUID DEFAULT NEWID() ROWGUIDCOL,\r\n");
            sb.Append("	CONSTRAINT pk_{EntityTable} PRIMARY KEY CLUSTERED (Version_ID, ID),\r\n");
            sb.Append("	CONSTRAINT fk_{EntityTable}_tblModelVersion_Version_ID FOREIGN KEY (Version_ID) REFERENCES mdm.tblModelVersion(ID) ON UPDATE NO ACTION ON DELETE NO ACTION,\r\n");
            sb.Append("	CONSTRAINT ck_{EntityTable}_Status_ID CHECK (Status_ID BETWEEN 1 AND 2),\r\n");
            sb.Append("	CONSTRAINT ck_{EntityTable}_ValidationStatus_ID CHECK (ValidationStatus_ID BETWEEN 0 and 5)\r\n");
            sb.Append(")\r\n");
            sb.Append("CREATE UNIQUE NONCLUSTERED INDEX ux_{EntityTable}_Version_ID_Code ON mdm.{EntityTable}(Version_ID, Code)\r\n");
            sb.Append("CREATE NONCLUSTERED INDEX ix_{EntityTable}_Version_ID_Name ON mdm.{EntityTable}(Version_ID, Name)\r\n");
            sb.Append("CREATE NONCLUSTERED INDEX ix_{EntityTable}_Version_ID_LastChgDTM ON mdm.{EntityTable}(Version_ID, LastChgDTM)\r\n");
            sb.Append("CREATE UNIQUE NONCLUSTERED INDEX ux_{EntityTable}_MUID ON mdm.{EntityTable}(MUID)\r\n");
            sb.Append("CREATE NONCLUSTERED INDEX ix_{EntityTable}_Version_ID_AsOf_ID ON mdm.{EntityTable}(Version_ID, AsOf_ID) WHERE [AsOf_ID] IS NOT NULL\r\n");
            sb.Append("';\r\n");
            sb.Append("\r\n");
            sb.Append("SET @SQL = REPLACE(@SQL, N'{EntityTable}', @EntityTable);\r\n");
            sb.Append("\r\n");
            sb.Append("EXEC sp_executesql @SQL;\r\n");
            sb.Append("\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Staging Table\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("IF COALESCE(@StagingTable, N'') <> N'' BEGIN\r\n");
            sb.Append("	SET @SQL = N'  \r\n");
            sb.Append("		CREATE TABLE [stg].{StagingTable}\r\n" );
            sb.Append("		(\r\n");
            sb.Append("			ID                  INT IDENTITY (1, 1) NOT NULL,\r\n");
            sb.Append("			ImportType          TINYINT NOT NULL,\r\n");
            sb.Append("			ImportStatus_ID     TINYINT NOT NULL CONSTRAINT df_{StagingTable}_ImportStatus_ID DEFAULT 0,\r\n");
            sb.Append("			Batch_ID            INT NULL,\r\n");
            sb.Append("			BatchTag            NVARCHAR(50) NULL,\r\n");
            sb.Append("			ErrorCode           INT,\r\n");
            sb.Append("			[SystemUserName]    [nvarchar](128) NULL CONSTRAINT [DF_{StagingTable}_SystemUserName]  DEFAULT (suser_sname()),\r\n");
            sb.Append("			[UserID]            INT,\r\n");
            sb.Append("			Code                NVARCHAR(250) NULL,\r\n");
            sb.Append("			[Name]              NVARCHAR(250) NULL,\r\n");
            sb.Append("			NewCode             NVARCHAR(250) NULL,\r\n");  
            sb.Append("			CONSTRAINT pk_{StagingTable} PRIMARY KEY CLUSTERED (ID),\r\n");
            sb.Append("			CONSTRAINT ck_{StagingTable}_ImportType CHECK (ImportType BETWEEN 0 AND 6),\r\n");
            sb.Append("			CONSTRAINT ck_{StagingTable}_ImportStatus_ID CHECK (ImportStatus_ID BETWEEN 0 and 3)\r\n");
            sb.Append("		)\r\n");
            sb.Append("		CREATE NONCLUSTERED INDEX ix_{StagingTable}_Batch_ID ON [stg].{StagingTable}(Batch_ID)\r\n");
            sb.Append("		CREATE NONCLUSTERED INDEX ix_{StagingTable}_BatchTag ON [stg].{StagingTable}(BatchTag)\r\n");
            sb.Append("		';\r\n");
            sb.Append("\r\n");
            sb.Append("SET @SQL = REPLACE(@SQL, N'{StagingTable}', @StagingTable);\r\n");
            sb.Append("\r\n");
            sb.Append("EXEC sp_executesql @SQL;\r\n");
            sb.Append("\r\n");
            sb.Append("END;\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Security Table\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("SET @SQL = N'\r\n");
            sb.Append("	CREATE TABLE mdm.{SecurityTable}\r\n");
            sb.Append("	(\r\n");
            sb.Append("		ID                  INT IDENTITY (1, 1) NOT NULL,\r\n");
            sb.Append("		Version_ID          INT NOT NULL,\r\n");
            sb.Append("		SecurityRole_ID     INT NOT NULL,\r\n");
            sb.Append("		MemberType_ID       TINYINT NOT NULL,\r\n");
            sb.Append("		EN_ID               INT NULL,\r\n");
            sb.Append("		HP_ID               INT NULL,\r\n");
            sb.Append("		Privilege_ID        TINYINT NOT NULL,\r\n");
            sb.Append("		CONSTRAINT [PK_{SecurityTable}] PRIMARY KEY NONCLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],\r\n");
            sb.Append("     CONSTRAINT fk_{SecurityTable}_{EntityTable}_Version_ID_EN_ID FOREIGN KEY (Version_ID, EN_ID) REFERENCES mdm.{EntityTable}(Version_ID, ID) ON UPDATE NO ACTION ON DELETE CASCADE,\r\n");
            sb.Append("		CONSTRAINT fk_{SecurityTable}_tblSecurityRole_SecurityRole_ID FOREIGN KEY (SecurityRole_ID) REFERENCES mdm.tblSecurityRole(ID) ON UPDATE NO ACTION ON DELETE CASCADE,\r\n");
            sb.Append("		CONSTRAINT ck_{SecurityTable}_MemberType_ID CHECK ((MemberType_ID = 1 AND EN_ID IS NOT NULL AND HP_ID IS NULL) OR (MemberType_ID = 2 AND HP_ID IS NOT NULL AND EN_ID IS NULL)),\r\n");
            sb.Append("		CONSTRAINT fk_{SecurityTable}_Privilege_ID CHECK (Privilege_ID BETWEEN 1 AND 3)\r\n");
            sb.Append("	) ON [PRIMARY]\r\n");
            sb.Append("	CREATE UNIQUE CLUSTERED INDEX ux_{SecurityTable}_Version_ID_SecurityRole_ID_MemberType_ID_EN_ID_HP_ID ON mdm.{SecurityTable}(Version_ID, SecurityRole_ID, MemberType_ID, EN_ID, HP_ID)\r\n");
            sb.Append("	CREATE UNIQUE NONCLUSTERED INDEX ux_{SecurityTable}_Version_ID_EN_ID_SecurityRole_ID ON mdm.{SecurityTable}(Version_ID, EN_ID, SecurityRole_ID) WHERE [EN_ID] IS NOT NULL\r\n");
            sb.Append("	CREATE UNIQUE NONCLUSTERED INDEX ux_{SecurityTable}_Version_ID_HP_ID_SecurityRole_ID ON mdm.{SecurityTable}(Version_ID, HP_ID, SecurityRole_ID) WHERE [HP_ID] IS NOT NULL\r\n");
            sb.Append("	CREATE NONCLUSTERED INDEX ix_{SecurityTable}_SecurityRole_ID ON mdm.{SecurityTable}(SecurityRole_ID)\r\n");
            sb.Append("	';\r\n");
            sb.Append("\r\n");
            sb.Append("SET @SQL = REPLACE(@SQL, N'{SecurityTable}', @SecurityTable);\r\n");
            sb.Append("SET @SQL = REPLACE(@SQL, N'{EntityTable}', @EntityTable);\r\n");
            sb.Append("EXEC sp_executesql @SQL;\r\n");
            sb.Append("\r\n");
            sb.Append("EXEC mdm.udpCreateViews @Model_ID, @Entity_ID;\r\n");
            sb.Append("EXEC mdm.udpCreateEntityStagingErrorDetailViews @Entity_ID;\r\n");
            sb.Append("EXEC mdm.udpEntityStagingCreateLeafStoredProcedure @Entity_ID\r\n");
            sb.Append("PRINT 'Slave Create Entity : " + ae.ce.Name + " Complete.'\r\n");
            sb.Append("GO\r\n");
        }
        private void buildEntityCreateMaster(ActionEntity ae, StringBuilder sb)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Create Entity : " + ae.ce.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("\r\n");
            if (ae.syncGuid == true)
            {
                sb.Append("DECLARE @Return_MUID UNIQUEIDENTIFIER\r\n");
                sb.Append("DECLARE @Return_ID INT\r\n");
                sb.Append("SET @Return_MUID = '" + ae.ce.EntityMUID + "'\r\n");
            }
            sb.Append("-- get UserID for: " + CurrentUser + "\r\n");
            sb.Append("DECLARE @UserID INT\r\n");
            sb.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = '" + CurrentUser + "'\r\n");
            sb.Append("EXEC [mdm].[udpEntitySaveByMUID]\r\n");
            sb.Append("@User_ID = @UserID\r\n");
            sb.Append(",@Model_MUID = NULL\r\n");
            sb.Append(",@Model_Name = '" + Server.Model + "'\r\n");
            sb.Append(",@Entity_MUID = NULL\r\n");
            sb.Append(",@Entity_Name = '" + ae.ce.Name + "'\r\n");

            if (ae.syncGuid == true)
            {
                sb.Append(",@Return_MUID = @Return_MUID OUTPUT\r\n");
                sb.Append(",@Return_ID = @Return_ID OUTPUT\r\n");
            }

            if (Server.schemaVersion.VersionDetails.HasCodeGenSeed)
            {
                //Sending a null will set it to 0
                if (ae.ce.CodeGenSeed == 1)
                {
                    sb.Append(",@CodeGenSeed = " + ae.ce.CodeGenSeed + "\r\n");
                }
            }
            if (Server.schemaVersion.VersionDetails.HasStagingTable)
            {
                bool bStagingDifferent = false;
                if (Server.Model + "_" + ae.ce.Name == ae.ce.StagingBase)
                {
                    sb.Append(",@StagingBase = '" + ae.ce.StagingBase + "'\r\n");
                }
                else
                {
                    sb.Append(",@StagingBase = '" + Server.Model + "_" + ae.ce.Name + "'\r\n");
                    bStagingDifferent = true;
                }

                if (bStagingDifferent)
                {
                    sb.Append("PRINT 'WARNING StagingBase is not the same name as source. Due to Model been different.'\r\n");
                }
            }

            if (ae.syncGuid == true)
            {
                foreach (var attrbitue in ae.ce.Attributes.Values)
                {
                    sb.Append("UPDATE mdm.tblAttribute SET [MUID] = '" + attrbitue.MUID +
                              "' WHERE Entity_ID = @Return_ID AND name = '" + attrbitue.Name + "'\r\n");
                }
            }

            sb.Append("\r\n");
            sb.Append("PRINT 'Master Create Entity : " + ae.ce.Name + " Complete.'\r\n");
            sb.Append("GO\r\n");
        }
#endregion
        #endregion

        #region Views
        private void buildViews(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB)
        {
            foreach (var av in LActionViews)
            {
                switch(av.status)
                {
                    case ObjectStatus.Different:
                        {
                            buildViewRebuild(dSB, av);
                        }
                        break;
                    case ObjectStatus.Missing:
                        {
                            buildViewCreate(dSB, av);
                        }
                        break;
                    case ObjectStatus.Delete:
                        {
                            buildViewDelete(dSB, av);
                        }
                        break;
                    case ObjectStatus.Rename:
                        {
                            buildViewRebuild(dSB, av);
                        }
                        break;
                    default:
                        throw new NotImplementedException();
                }
            }

            if (!String.IsNullOrEmpty(dSB[DeploymentBuildOrder.Master_AddChangeDeleteView].Query.ToString()))
            {
                //ReplicationCheckPendingCount(dSB[DeploymentBuildOrder.Master_AddChangeDeleteView].Query);
                ReplicationCheckRequestResponse(dSB[DeploymentBuildOrder.Master_AddChangeDeleteView].Query);
            }
        }

        private void buildViewRebuild(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionView av)
        {
            buildViewRebuild(dSB[DeploymentBuildOrder.Master_AddChangeDeleteView], av);
            buildViewRebuild(dSB[DeploymentBuildOrder.Slave_AddChangeDeleteView], av);
        }

        private void buildViewCreate(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionView av)
        {
            buildViewCreateMaster(dSB[DeploymentBuildOrder.Master_AddChangeDeleteView].Query, av);
            buildViewRebuild(dSB[DeploymentBuildOrder.Slave_AddChangeDeleteView], av);
        }

        private void buildViewCreateMaster(StringBuilder sb, ActionView av)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Create Subscription View: " + av.cv.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("\r\n");
            sb.Append("DECLARE @SQLCreate NVARCHAR(MAX)\r\n");
            sb.Append("SELECT @SQLCreate =\r\n");
            sb.Append("'EXEC mdm.udpSubscriptionViewSave @SubscriptionView_ID=NULL,@Entity_ID=' + CONVERT(VARCHAR(10), Entity_ID) + '\r\n");
            sb.Append(",@Model_ID=' + CONVERT(VARCHAR(10), Model_ID) + '\r\n");
            sb.Append(",@DerivedHierarchy_ID=NULL,@ModelVersion_ID=' + CONVERT(VARCHAR(10), ModelVersion_ID) + '\r\n");
            sb.Append(",@ModelVersionFlag_ID=NULL,@ViewFormat_ID='  + CONVERT(VARCHAR(10), ViewFormat_ID) + ',@Levels=NULL,@SubscriptionViewName=N''" + av.cv.Name + "'''\r\n");
            sb.Append("FROM (\r\n");
            sb.Append("\tSELECT e.ID AS 'Entity_ID',\r\n");
            sb.Append("\tm.ID AS 'Model_ID',\r\n");
            sb.Append("\tmv.ID AS 'ModelVersion_ID',\r\n");
            sb.Append("\t" + av.cv.ViewFormatID + " AS 'ViewFormat_ID',\r\n");
            sb.Append("\tm.Name + '_' + e.Name AS 'Name'\r\n");
            sb.Append("\tFROM mdm.tblModel m\r\n");
            sb.Append("\tINNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID\r\n");
            sb.Append("\tINNER JOIN mdm.tblEntity e ON m.ID = e.Model_ID\r\n");
            sb.Append("\tLEFT JOIN INFORMATION_SCHEMA.TABLES it ON it.TABLE_SCHEMA = 'mdm' AND it.TABLE_NAME = m.Name + '_' + e.Name\r\n");
            sb.Append("\tWHERE m.Name = '" + Server.Model + "'\r\n");
            sb.Append("\tAND e.Name = '" + av.cv.EntityName  + "'\r\n");
            sb.Append("\tAND mv.Name = '" + Server.ModelVersion + "')a\r\n"); //This needs to be fixed could be building view for different versions
            sb.Append("EXEC sp_executesql @SQLCreate\r\n");
            sb.Append("\r\n");
            sb.Append("GO\r\n");

        }

        private void buildViewRebuild(DeploymentStep dStep, ActionView av)
        {
            var sb = dStep.Query;
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Create/Rebuild Subscription View: " + av.cv.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("\r\n");

            if (dStep.IsMaster)
            {
                sb.Append("UPDATE mdm.tblSubscriptionView SET NAME = '" + av.cv.Name + "'\r\n");
                sb.Append("FROM mdm.tblSubscriptionView sv\r\n");
                sb.Append("INNER JOIN mdm.tblEntity e ON e.ID = sv.Entity_ID\r\n");
                sb.Append("WHERE e.MUID = '" + av.cv.EntityMUID + "'\r\n");

                if (av.status == ObjectStatus.Rename)
                {
                    sb.Append("AND sv.Name = '" + av.cvDestination.Name + "'\r\n");
                }
                else
                {
                    sb.Append("AND sv.Name = '" + av.cv.Name + "'\r\n");

                }
            }
            
            sb.Append("DECLARE @SubscriptionViewID       INT, \r\n");
            sb.Append("@ViewEntity_ID            INT,\r\n");
            sb.Append("@ViewModel_ID             INT,\r\n");
            sb.Append("@DerivedHierarchy_ID      INT,\r\n");
            sb.Append("@ViewFormat_ID            INT,\r\n");
            sb.Append("@ModelVersion_ID          INT,\r\n");
            sb.Append("@ModelVersionFlag_ID      INT,\r\n");
            sb.Append("@SubscriptionViewName     sysname,\r\n");
            sb.Append("@Levels                   INT;\r\n");
            sb.Append("\r\n");
            sb.Append("SELECT \r\n");
            sb.Append("@SubscriptionViewID = sv.ID,\r\n");
            sb.Append("@ViewEntity_ID = Entity_ID,\r\n");
            sb.Append("@ViewModel_ID = sv.Model_ID,\r\n");
            sb.Append("@DerivedHierarchy_ID = DerivedHierarchy_ID,\r\n");
            sb.Append("@ViewFormat_ID = ViewFormat_ID,\r\n");
            sb.Append("@ModelVersion_ID = ModelVersion_ID,\r\n");
            sb.Append("@ModelVersionFlag_ID = ModelVersionFlag_ID,\r\n");
            sb.Append("@SubscriptionViewName = sv.Name,\r\n");
            sb.Append("@Levels = Levels\r\n");
            sb.Append("FROM mdm.tblSubscriptionView sv\r\n");
            sb.Append("INNER JOIN mdm.tblEntity e ON e.ID = sv.Entity_ID\r\n");
            sb.Append("WHERE e.MUID = '" + av.cv.EntityMUID + "'\r\n");
            sb.Append("AND sv.Name = '" + av.cv.Name + "'\r\n");
            sb.Append("\r\n");
            sb.Append("EXEC [mdm].[udpCreateSubscriptionViews] @SubscriptionViewID, @ViewEntity_ID, @ViewModel_ID, @DerivedHierarchy_ID, @ModelVersion_ID, @ModelVersionFlag_ID, @ViewFormat_ID, @Levels, @SubscriptionViewName;\r\n");
            sb.Append("GO\r\n");
        }

        private void buildViewDelete(Dictionary<DeploymentBuildOrder, DeploymentStep> dSB, ActionView av)
        {
            buildViewDeleteMaster(dSB[DeploymentBuildOrder.Master_AddChangeDeleteView].Query, av);
            buildViewDeleteSlave(dSB[DeploymentBuildOrder.Slave_AddChangeDeleteView].Query, av);
        }

        private void buildViewDeleteMaster(StringBuilder sb, ActionView av)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Master Delete Subscription View: " + av.cvDestination.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("\r\n");
            sb.Append("DECLARE @SQL NVARCHAR(MAX)\r\n");
            sb.Append("SELECT @SQL = 'EXEC mdm.udpSubscriptionViewDeleteByID @ID=' + CONVERT(VARCHAR(10), ID) + ' ,@DeleteView=1'\r\n");
            sb.Append("FROM mdm.tblSubscriptionView\r\n");
            sb.Append("WHERE Name = '" + av.cvDestination.Name + "'\r\n");
            sb.Append("EXEC sp_executesql @SQL\r\n");
            sb.Append("PRINT 'Master Delete Subscription View: " + av.cvDestination.Name + " Complete'\r\n");
            sb.Append("GO\r\n");
        }

        private void buildViewDeleteSlave(StringBuilder sb, ActionView av)
        {
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Slave Delete Subscription View: " + av.cvDestination.Name + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("\r\n");
            sb.Append("IF EXISTS(SELECT 1 FROM sys.views WHERE [name] = '" + av.cvDestination.Name + "' AND [schema_id] = SCHEMA_ID('mdm'))\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("	DROP VIEW mdm." + av.cvDestination.Name + "\r\n");
            sb.Append("END\r\n");
            sb.Append("PRINT 'Slave Delete Subscription View: " + av.cvDestination.Name + " Complete'\r\n");
            sb.Append("GO\r\n");
        }

        #endregion

        #region Members
        public void buildMembers(StringBuilder sb)
        {
            bool isFistSelectStatement = true;
            bool isHeaderBuilt = false;
            int batchCount = 0;

            if (LActionEntityMembers.Count > 0)
            {
                foreach (var LActionEntityMember in LActionEntityMembers)
                {
                    if (LActionEntityMember.LActionMembers.Count == 0)
                    {
                        continue;
                    }
                    ActionEntityMember actionSpecialEntityMembers = new ActionEntityMember();
                    actionSpecialEntityMembers.EntityName = LActionEntityMember.EntityName;
                    ActionMember actionMembers = new ActionMember();

                    sb.Append("DECLARE @BatchTag NVARCHAR(50)\r\n");
                    sb.Append("SELECT @BatchTag = REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')\r\n");
                    sb.Append("PRINT 'BatchTag Details: ' + @BatchTag\r\n");

                    string sqlQueryHeader = "INSERT INTO [stg].[" + LActionEntityMember.EntityStagingTableName +
                                            "_Leaf]\r\n";
                    sqlQueryHeader += "(\r\n" +
                                      "\t[ImportType]\r\n";
                    sqlQueryHeader += "\t,[ImportStatus_ID]\r\n";
                    sqlQueryHeader += "\t,[BatchTag]\r\n";

                    foreach (var a in LActionEntityMember.LActionMembers)
                    {

                        if (!isFistSelectStatement)
                        {
                            sb.Append(" UNION ALL\r\n");
                        }

                        string memberValues = "";
                        if (isFistSelectStatement)
                        {
                            memberValues = "SELECT 0 AS 'ImportType', 0 AS 'ImportStatus_ID', @BatchTag AS 'BatchTag'";
                        }
                        else
                        {
                            memberValues = "SELECT 0, 0, @BatchTag";
                        }


                        foreach (var m in a.lMemberValue)
                        {
                            if (m.ValueSource == null)
                            {
                                if (m.ValueDestination == null)
                                {
                                    memberValues += ", NULL";
                                }
                                else
                                {
                                    //may have to extend to handle domain attributes where the code value is less than the nullable lenght
                                    if ((m.dataType == MDSDataTypes.String && m.Attribute.isDomainAttribute == false &&
                                         m.Attribute.DataTypeInformation <
                                         Constants.MDS_STAGING_ENTITY_NULL_STRING.Length &&
                                         m.MemberName.ToUpper() != Constants.NAME) ||
                                        (m.MemberName.ToUpper() == Constants.NAME))
                                    {
                                        memberValues += ", NULL";
                                        actionMembers.lMemberValue.Add(m);
                                    }
                                    else
                                    {
                                        memberValues += ", " + MDSHelper.GetMDSNullValue(m.dataType, m.MemberName);
                                    }
                                }

                            }
                            else
                            {
                                if (m.ValueDestination != null && m.dataType == MDSDataTypes.String &&
                                    m.ValueDestination != m.ValueSource &&
                                    m.ValueDestination.ToLower() == m.ValueSource.ToLower())
                                {
                                    memberValues += ", NULL";

                                    actionMembers.lMemberValue.Add(m);
                                }
                                else
                                {
                                    memberValues += ", N'" + singleQuoteCheck(m.ValueSource) + "'";
                                }
                            }


                            if (isFistSelectStatement)
                            {
                                if (!isHeaderBuilt)
                                {
                                    sqlQueryHeader += "\t,[" + m.MemberName + "]\r\n";
                                }
                                memberValues += " AS '" + m.MemberName + "'";
                            }
                        }

                        if (isFistSelectStatement)
                        {
                            sb.Append(sqlQueryHeader + ")\r\n");
                            isFistSelectStatement = false;
                            isHeaderBuilt = true;
                        }

                        sb.Append(memberValues);

                        batchCount++;

                        if (batchCount > 100)
                        {
                            batchCount = 0;
                            isFistSelectStatement = true;
                            sb.Append("\r\n\r\n");
                        }

                    }

                    sb.Append("\r\n\r\n");
                    sb.Append("EXEC stg.udp_" + LActionEntityMember.EntityStagingTableName + "_Leaf '" +
                              Server.ModelVersion + "', 1, @BatchTag\r\n");
                    sb.Append("GO\r\n");

                    if (actionMembers.lMemberValue.Count > 0)
                    {
                        actionSpecialEntityMembers.LActionMembers.Add(actionMembers);
                        
                        buildSpecialMembers(sb, actionSpecialEntityMembers);
                    }

                    isFistSelectStatement = true;
                    isHeaderBuilt = false;
                    batchCount = 0;
                }
            }
        }

        private string singleQuoteCheck(string valueSource)
        {
            return valueSource.Replace("'", "''");
        }

        public void buildSpecialMembers(StringBuilder sb, ActionEntityMember actionSpecialEntityMembers)
        {
            sb.Append("DECLARE @p9 mdm.MemberAttributes\r\n");
            foreach (var mb in actionSpecialEntityMembers.LActionMembers[0].lMemberValue)
            {
                string value = "";
                if(String.IsNullOrEmpty(mb.ValueSource))
                {
                    value = "NULL";
                }
                else
                {
                    value = "N'" + mb.ValueSource + "'";
                }

                sb.Append("INSERT INTO @p9 VALUES(N'" + mb.CodeValue + "',N'" + mb.MemberName + "'," + value + ",NULL,NULL,NULL)\r\n");
            }
            
            sb.Append("\r\n");
            sb.Append("-- get UserID for: " + CurrentUser + "\r\n");
            sb.Append("DECLARE @UserID INT\r\n");
            sb.Append("SELECT @UserID = ID FROM mdm.tblUser WHERE Username = '" + CurrentUser + "'\r\n");
            sb.Append("\r\n");

            sb.Append("DECLARE @Version_ID INT\r\n");
            sb.Append("DECLARE @Entity_ID INT\r\n");
            sb.Append("SELECT @Entity_ID = e.ID, @Version_ID = mv.ID FROM mdm.tblEntity e\r\n");
            sb.Append("INNER JOIN mdm.tblModel m ON m.ID = e.Model_ID\r\n");
            sb.Append("INNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID\r\n");
            sb.Append("WHERE e.Name = '" + actionSpecialEntityMembers.EntityName + "'\r\n");
            sb.Append("AND m.Name = '" + Server.Model + "'\r\n");
            sb.Append("\r\n");

            sb.Append("EXEC mdm.udpEntityMembersUpdate @User_ID=@UserID,@Version_ID=@Version_ID,@Entity_ID=@Entity_ID,\r\n");
            sb.Append("@MemberType_ID=1,@LogFlag=1,\r\n");
            sb.Append("@DoInheritanceRuleCheck=1,\r\n");
            sb.Append("@ShouldReturnMembersWithErrorsAsXml=0,\r\n");
            sb.Append("@IgnorePriorValues=1,\r\n");
            sb.Append("@MemberAttributes=@p9\r\n");
            sb.Append("GO\r\n");
        }

        #endregion

        #region Replication

        private void ReplicationCheckRequestResponse(StringBuilder sb)
        {
            if (IsReplicated)
            {
                sb.Append("-------------------------------------------------------------------------------\r\n");
                sb.Append("-- Replication Check Transaction Request Response\r\n");
                sb.Append("-------------------------------------------------------------------------------\r\n");
                sb.Append("SET NOCOUNT ON\r\n");
                sb.Append("DECLARE @requesttime DATETIME\r\n");
                sb.Append("DECLARE @description NVARCHAR(4000)\r\n");
                sb.Append("DECLARE @request_id INT\r\n");
                sb.Append("DECLARE @Loop INT = 1\r\n");
                sb.Append("DECLARE @Message VARCHAR(MAX)\r\n");
                sb.Append("DECLARE @Publication SYSNAME\r\n");
                sb.Append("\r\n");
                sb.Append("SELECT DISTINCT @Publication = s.publication\r\n");
                sb.Append("FROM distribution.dbo.MSdistribution_agents s with (nolock)\r\n");
                sb.Append("WHERE s.publisher_db = '" + Server.Database + "'\r\n");
                sb.Append("\r\n");
                sb.Append("IF OBJECT_ID('tempdb..#requests') IS NOT NULL\r\n");
                sb.Append("   DROP TABLE #requests\r\n");
                sb.Append("\r\n");
                sb.Append("CREATE TABLE #requests\r\n");
                sb.Append("(\r\n");
                sb.Append("   id int,\r\n");
                sb.Append("   publication sysname,\r\n");
                sb.Append("   sent_date datetime,\r\n");
                sb.Append("   description nvarchar(4000)\r\n");
                sb.Append(")\r\n");
                sb.Append("\r\n");
                sb.Append("IF OBJECT_ID('tempdb..#responses') IS NOT NULL\r\n");
                sb.Append("   DROP TABLE #responses\r\n");
                sb.Append("\r\n");
                sb.Append("CREATE TABLE #responses\r\n");
                sb.Append("(\r\n");
                sb.Append("   ServerName varchar(200) null,\r\n");
                sb.Append("   currenttime datetime null,\r\n");
                sb.Append("   sent_date datetime null,\r\n");
                sb.Append("   description nvarchar(4000),\r\n");
                sb.Append("   request_id int null,\r\n");
                sb.Append("   peer sysname null,\r\n");
                sb.Append("   peer_db sysname null,\r\n");
                sb.Append("   received_date datetime NULL\r\n");
                sb.Append(")\r\n");
                sb.Append("\r\n");
                sb.Append("EXEC sys.sp_requestpeerresponse \r\n");
                sb.Append("  @publication = @Publication,\r\n");
                sb.Append("  @description = @description,\r\n");
                sb.Append("  @request_id = @request_id OUTPUT;\r\n");
                sb.Append("SELECT @@ServerName AS ServerName, 'The ID of the new response request is ''' +  CONVERT(varchar,@request_id) + ''' [' + @description + ']' \r\n");
                sb.Append("\r\n");
                sb.Append("WHILE @Loop = 1\r\n");
                sb.Append("BEGIN\r\n");
                sb.Append("DELETE FROM #requests\r\n");
                sb.Append("DELETE FROM #responses\r\n");
                sb.Append("\r\n");
                sb.Append("-- Return request information to a temp table.\r\n");
                sb.Append("INSERT #requests (id, publication, sent_date, description)\r\n");
                sb.Append("EXEC sys.sp_helppeerrequests @publication = @Publication;\r\n");
                sb.Append("SELECT TOP 1 @request_id = id, @requesttime =  sent_date, @description = description FROM #requests ORDER BY id DESC \r\n");
                sb.Append("\r\n");
                sb.Append("-- Get response.\r\n");
                sb.Append("INSERT INTO #responses (request_id, peer, peer_db, received_date)\r\n");
                sb.Append("EXEC sys.sp_helppeerresponses @request_id=@request_id\r\n");
                sb.Append("\r\n");
                sb.Append("update #responses set currenttime = getdate(), ServerName = @@SERVERNAME, sent_date = @requesttime, description = @description\r\n");
                sb.Append("\r\n");
                sb.Append("IF EXISTS(SELECT * FROM #responses WHERE received_date IS NULL)\r\n");
                sb.Append("BEGIN\r\n");
                sb.Append("   SELECT @Message = '-- Waiting on transaction to be replicated. ' + CONVERT(VARCHAR(23), GETUTCDATE(), 121)\r\n");
                sb.Append("   RAISERROR (@Message, 0, 1) WITH NOWAIT\r\n");
                sb.Append("END\r\n");
                sb.Append("ELSE\r\n");
                sb.Append("BEGIN\r\n");
                sb.Append("   SELECT @Message = '-- Transaction replicated complete. ' + CONVERT(VARCHAR(23), GETUTCDATE(), 121)\r\n");
                sb.Append("   RAISERROR (@Message, 0, 1) WITH NOWAIT\r\n");
                sb.Append("   SET @Loop = 0\r\n");
                sb.Append("END\r\n");
                sb.Append("\r\n");
                sb.Append("IF @Loop = 1\r\n");
                sb.Append("BEGIN\r\n");
                sb.Append("   WAITFOR DELAY '00:00:10'\r\n");
                sb.Append("END\r\n");
                sb.Append("\r\n");
                sb.Append("END\r\n");
                sb.Append("\r\n");
                sb.Append("IF OBJECT_ID('tempdb..#requests') IS NOT NULL\r\n");
                sb.Append("   DROP TABLE #requests\r\n");
                sb.Append("\r\n");
                sb.Append("IF OBJECT_ID('tempdb..#responses') IS NOT NULL\r\n");
                sb.Append("   DROP TABLE #responses\r\n");
                sb.Append("\r\n");
                sb.Append("GO\r\n");
            }
        }
        private void ReplicationCheckPendingCount(StringBuilder sb)
        {
            if (IsReplicated)
            {
                sb.Append("-------------------------------------------------------------------------------\r\n");
                sb.Append("-- Replication Check Pending Count Start\r\n");
                sb.Append("-------------------------------------------------------------------------------\r\n");
                sb.Append("DECLARE @Loop INT = 1\r\n");
                sb.Append("DECLARE @CheckAgain INT = 1\r\n");
                sb.Append("DECLARE @sql VARCHAR(8000)\r\n");
                sb.Append("DECLARE @Message VARCHAR(MAX)\r\n");
                sb.Append("DECLARE @ErrorMessage VARCHAR(MAX)\r\n");
                sb.Append("DECLARE @Publisher_db SYSNAME\r\n");
                sb.Append("DECLARE @Publication SYSNAME\r\n");
                sb.Append("DECLARE @Subscriber_db SYSNAME\r\n");
                sb.Append("DECLARE @PublisherServer SYSNAME\r\n");
                sb.Append("DECLARE  @SubscriberServer SYSNAME\r\n");
                sb.Append("\r\n");
                sb.Append("IF OBJECT_ID('tempdb..#XP_CMDSHELL_RESULTS') IS NOT NULL\r\n");
                sb.Append("    DROP TABLE #XP_CMDSHELL_RESULTS\r\n");
                sb.Append("CREATE TABLE #XP_CMDSHELL_RESULTS (OUTPUT VARCHAR(MAX))\r\n");
                sb.Append("\r\n");
                sb.Append("IF OBJECT_ID('tempdb..#PublicationDetails') IS NOT NULL\r\n");
                sb.Append("    DROP TABLE #PublicationDetails\r\n");
                sb.Append("\r\n");
                sb.Append("-- logic taken from [sys].[sp_replmonitorhelpsubscription] as result can not be stored in temp table\r\n");
                sb.Append("SELECT DISTINCT case WHEN s.anonymous_subid IS NOT NULL \r\n");
                sb.Append("                           THEN  -- anonymous subscription\r\n");
                sb.Append("                        UPPER(s.subscriber_name)    -- name is stored in subscriber_name instead of sys.servers\r\n");
                sb.Append("                     ELSE\r\n");
                sb.Append("                        UPPER(subsrv.name)\r\n");
                sb.Append("                     END AS 'Subscriber',\r\n");
                sb.Append("                s.publisher_db, \r\n");
                sb.Append("                s.publication, \r\n");
                sb.Append("                s.subscriber_db,\r\n");
                sb.Append("                UPPER(pubsrv.name) AS 'Publisher'\r\n");
                sb.Append("INTO #PublicationDetails\r\n");
                sb.Append("FROM distribution.dbo.MSdistribution_agents s with (nolock)\r\n");
                sb.Append("INNER JOIN master.sys.servers as subsrv on subsrv.server_id = s.subscriber_id\r\n");
                sb.Append("INNER JOIN master.sys.servers as pubsrv on pubsrv.server_id = s.publisher_id\r\n");
                sb.Append("WHERE s.publisher_db = s.subscriber_db\r\n");
                sb.Append("AND s.publisher_db = '" + Server.Database + "'\r\n");
                sb.Append("\r\n");
                sb.Append("SELECT DISTINCT @Publisher_db = publisher_db, @Publication = publication, @Subscriber_db = subscriber_db FROM #PublicationDetails\r\n");
                sb.Append("\r\n");
                sb.Append("SET NOCOUNT ON\r\n");
                sb.Append("WHILE @Loop = 1\r\n");
                sb.Append("BEGIN \r\n");
                sb.Append("DELETE FROM #XP_CMDSHELL_RESULTS\r\n");
                sb.Append("\r\n");
                sb.Append("DECLARE curRepCheck CURSOR FOR\r\n");
                sb.Append("SELECT pubs.Server AS 'PublisherServer', subs.Server AS 'SubscriberServer' FROM \r\n");
                sb.Append("(\r\n");
                sb.Append("   SELECT Subscriber AS 'Server' FROM #PublicationDetails\r\n");
                sb.Append("   UNION\r\n");
                sb.Append("   SELECT Publisher AS 'Server' FROM #PublicationDetails\r\n");
                sb.Append(") pubs\r\n");
                sb.Append("LEFT JOIN\r\n");
                sb.Append("(\r\n");
                sb.Append("   SELECT Subscriber AS 'Server' FROM #PublicationDetails\r\n");
                sb.Append("   UNION\r\n");
                sb.Append("   SELECT Publisher AS 'Server' FROM #PublicationDetails\r\n");
                sb.Append(")subs ON pubs.Server <> subs.Server\r\n");
                sb.Append("OPEN curRepCheck\r\n");
                sb.Append("FETCH NEXT FROM curRepCheck INTO @PublisherServer, @SubscriberServer\r\n");
                sb.Append("WHILE (@@FETCH_STATUS = 0)\r\n");
                sb.Append("BEGIN\r\n");
                sb.Append("\r\n");
                sb.Append("-- Get counts from all servers in replicated cluster\r\n");
                sb.Append(@"-- Use xp_cmdshell/sqlcmd to avoid openrowset - 'Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.' \r\n");
                sb.Append("-- Use openrowset to store result of sp_replmonitorsubscriptionpendingcmds manipulate result\r\n");
                sb.Append("   SELECT @sql ='sqlcmd -q \"SELECT MAX(CASE WHEN pendingcmdcount > 0 THEN ''IsPendingCount = 1'' ELSE ''IsPendingCount = 0'' END) FROM ( SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @PublisherServer + ';Trusted_Connection=yes;'', ''EXEC [distribution].dbo.sp_replmonitorsubscriptionpendingcmds @publisher =''''' + @PublisherServer + ''''', @publisher_db=''''' + @Publisher_db + ''''', @publication =''''' + @Publication + ''''', @subscriber =''''' + @SubscriberServer + ''''', @subscriber_db = ''''' + @Subscriber_db + ''''', @subscription_type =''''0'''''')) a \" -S ' + @PublisherServer + ' -E' \r\n");
                sb.Append("   --PRINT @sql\r\n");
                sb.Append("   INSERT INTO #XP_CMDSHELL_RESULTS\r\n");
                sb.Append("   EXEC master..xp_cmdshell @sql\r\n");
                sb.Append("\r\n");
                sb.Append("	FETCH NEXT FROM curRepCheck INTO @PublisherServer, @SubscriberServer\r\n");
                sb.Append("END\r\n");
                sb.Append("CLOSE curRepCheck;\r\n");
                sb.Append("DEALLOCATE curRepCheck;\r\n");
                sb.Append("\r\n");
                sb.Append("DELETE FROM #XP_CMDSHELL_RESULTS WHERE OUTPUT IS NULL\r\n");
                sb.Append("DELETE FROM #XP_CMDSHELL_RESULTS WHERE OUTPUT LIKE '(% rows affected)'\r\n");
                sb.Append("DELETE FROM #XP_CMDSHELL_RESULTS WHERE OUTPUT = ''\r\n");
                sb.Append("DELETE FROM #XP_CMDSHELL_RESULTS WHERE OUTPUT = '------------------'\r\n");
                sb.Append("\r\n");
                sb.Append("--Have to use RAISERROR as feed back because of WAITFOR transaction effect.\r\n");
                sb.Append("IF EXISTS(SELECT * FROM #XP_CMDSHELL_RESULTS WHERE output NOT LIKE 'IsPendingCount%')\r\n");
                sb.Append("BEGIN\r\n");
                sb.Append("	SET @CheckAgain = 1\r\n");
                sb.Append("	SELECT TOP 1 @ErrorMessage = output FROM #XP_CMDSHELL_RESULTS WHERE output NOT LIKE 'IsPendingCount%'\r\n");
                sb.Append("	RAISERROR (@ErrorMessage, 0, 1) WITH NOWAIT \r\n");
                sb.Append("END\r\n");
                sb.Append("ELSE IF EXISTS(SELECT * FROM #XP_CMDSHELL_RESULTS WHERE output = 'IsPendingCount = 1')\r\n");
                sb.Append("BEGIN\r\n");
                sb.Append("	SET @CheckAgain = 1\r\n");
                sb.Append("	SELECT @Message = '-- pendingcmdcount > 0. ' + CONVERT(VARCHAR(23), GETUTCDATE(), 121)\r\n");
                sb.Append("	RAISERROR (@Message, 0, 1) WITH NOWAIT\r\n");
                sb.Append("END\r\n");
                sb.Append("ELSE IF EXISTS(SELECT * FROM #XP_CMDSHELL_RESULTS WHERE output = 'IsPendingCount = 0')\r\n");
                sb.Append("BEGIN\r\n");
                sb.Append("	IF @CheckAgain = 1\r\n");
                sb.Append("	BEGIN\r\n");
                sb.Append("	    SELECT @Message = '-- pendingcmdcount = 0. Checking again. ' + CONVERT(VARCHAR(23), GETUTCDATE(), 121)\r\n");
                sb.Append("	    RAISERROR (@Message, 0, 1) WITH NOWAIT\r\n");
                sb.Append("	END\r\n");
                sb.Append("	ELSE IF @CheckAgain = 0\r\n");
                sb.Append("	BEGIN\r\n");
                sb.Append("	    SELECT @Message = '-- pendingcmdcount = 0. Complete. ' + CONVERT(VARCHAR(23), GETUTCDATE(), 121)\r\n");
                sb.Append("	    RAISERROR (@Message, 0, 1) WITH NOWAIT\r\n");
                sb.Append("		SET @Loop = 0\r\n");
                sb.Append("	END\r\n");
                sb.Append("\r\n");
                sb.Append("	SET @CheckAgain = 0\r\n");
                sb.Append("END\r\n");
                sb.Append("\r\n");
                sb.Append("IF @Loop = 1\r\n");
                sb.Append("BEGIN\r\n");
                sb.Append("	WAITFOR DELAY '00:00:10'\r\n");
                sb.Append("END\r\n");
                sb.Append("\r\n");
                sb.Append("END\r\n");
                sb.Append("DROP TABLE #XP_CMDSHELL_RESULTS\r\n");
                sb.Append("DROP TABLE #PublicationDetails\r\n");
                sb.Append("-------------------------------------------------------------------------------\r\n");
                sb.Append("-- Replication Check Pending Count Complete\r\n");
                sb.Append("-------------------------------------------------------------------------------\r\n");
                sb.Append("\r\n");
            }
        }

        private void ReplicationJob(StringBuilder sb, bool jobStatus, string databaseName )
        {
            string jobAction = "";
            string jobEnabled = "";
            string ifAction = "";

            if (jobStatus)
            {
                jobAction = "start";
                jobEnabled = "1";
                ifAction = "NOT ";
            }
            else
            {
                jobAction = "stop";
                jobEnabled = "0";
                
            }

            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("-- Replication Job " + jobAction + "\r\n");
            sb.Append("-------------------------------------------------------------------------------\r\n");
            sb.Append("SET NOCOUNT ON\r\n");
            sb.Append("DECLARE @sql AS NVARCHAR(MAX)\r\n");
            sb.Append("DECLARE @Message AS NVARCHAR(MAX)\r\n");
            sb.Append("DECLARE @jobname SYSNAME\r\n");
            sb.Append("DECLARE @Loop INT = 1\r\n");
            sb.Append("DECLARE cur1 CURSOR FOR \r\n");
            sb.Append("SELECT [name] FROM [distribution].[dbo].[MSdistribution_agents]  where [subscriber_db] ='" + Server.Database + "'\r\n");
            sb.Append("OPEN cur1\r\n");
            sb.Append("FETCH NEXT FROM cur1 INTO @jobname\r\n");
            sb.Append("WHILE @@FETCH_STATUS = 0\r\n");
            sb.Append("BEGIN \r\n");
            sb.Append("   SET @Loop = 1\r\n");
            sb.Append("   --disable the job\r\n");
            sb.Append("   SELECT @sql = 'EXEC msdb.dbo.sp_update_job @job_name= ''' + @jobname + ''',  @enabled=" + jobEnabled + "'\r\n");
            sb.Append("   EXEC (@sql)\r\n");
            sb.Append("\r\n");
            sb.Append("   --stop the job\r\n");
            sb.Append("   RAISERROR ('--About to " + jobAction + " job', 0, 1) WITH NOWAIT\r\n");
            sb.Append("   IF " + ifAction + "EXISTS(SELECT job.name, job.job_id, job.originating_server, activity.run_requested_date, DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) as Elapsed\r\n");
            sb.Append("                  FROM msdb.dbo.sysjobs_view job \r\n");
            sb.Append("                  INNER JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id\r\n");
            sb.Append("                  INNER JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id\r\n");
            sb.Append("                  JOIN\r\n");
            sb.Append("                  (\r\n");
            sb.Append("                     SELECT MAX( agent_start_date ) AS max_agent_start_date\r\n");
            sb.Append("                     FROM msdb.dbo.syssessions\r\n");
            sb.Append("                  ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date\r\n");
            sb.Append("                  WHERE run_requested_date IS NOT NULL AND stop_execution_date IS NULL\r\n");
            sb.Append("                  AND job.name = @jobname)\r\n");
            sb.Append("    BEGIN\r\n");
            sb.Append("      SELECT @sql = 'EXEC msdb.dbo.sp_" + jobAction + "_job @job_name=''' + @jobname + ''''\r\n");
            sb.Append("      EXEC (@sql)\r\n");
            sb.Append("   END\r\n");
            sb.Append("   WHILE @Loop = 1\r\n");
            sb.Append("   BEGIN\r\n");
            sb.Append("      IF " + ifAction + "EXISTS(SELECT job.name, job.job_id, job.originating_server, activity.run_requested_date, DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) as Elapsed\r\n");
            sb.Append("                  FROM msdb.dbo.sysjobs_view job \r\n");
            sb.Append("                  INNER JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id\r\n");
            sb.Append("                  INNER JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id\r\n");
            sb.Append("                  JOIN\r\n");
            sb.Append("                  (\r\n");
            sb.Append("                     SELECT MAX( agent_start_date ) AS max_agent_start_date\r\n");
            sb.Append("                     FROM msdb.dbo.syssessions\r\n");
            sb.Append("                  ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date\r\n");
            sb.Append("                  WHERE run_requested_date IS NOT NULL AND stop_execution_date IS NULL\r\n");
            sb.Append("                  AND job.name = @jobname)\r\n");
            sb.Append("      BEGIN\r\n");
            sb.Append("         SELECT @Message = '-- waiting for job to " + jobAction + ": ' + @jobname + ' ' + CONVERT(VARCHAR(23), GETUTCDATE(), 121)\r\n");
            sb.Append("           RAISERROR (@Message, 0, 1) WITH NOWAIT\r\n");
            sb.Append("      END\r\n");
            sb.Append("      ELSE\r\n");
            sb.Append("      BEGIN\r\n");
            sb.Append("         SELECT @Message = '-- job " + jobAction + " complete: ' + @jobname + ' ' + CONVERT(VARCHAR(23), GETUTCDATE(), 121)\r\n");
            sb.Append("         RAISERROR (@Message, 0, 1) WITH NOWAIT\r\n");
            sb.Append("         SET @Loop = 0\r\n");
            sb.Append("      END\r\n");
            sb.Append("\r\n");
            sb.Append("      IF @Loop = 1\r\n");
            sb.Append("      BEGIN\r\n");
            sb.Append("         WAITFOR DELAY '00:00:10'\r\n");
            sb.Append("      END\r\n");
            sb.Append("   END\r\n");
            sb.Append("\r\n");
            sb.Append("   FETCH NEXT FROM cur1 INTO @jobname\r\n");
            sb.Append("END\r\n");
            sb.Append("CLOSE cur1\r\n");
            sb.Append("DEALLOCATE cur1\r\n");
            sb.Append("GO\r\n");

        }
        #region Entities
        private void ReplicationAddArticle(StringBuilder sb)
        {

            //only need to add once. As this loops to add all mdm tables.
            string sql = "-------------------------------------------------------------------------------\r\n"
                       + "-- Add Atricles to replication\r\n"
                       + "-------------------------------------------------------------------------------\r\n"
                       + "DECLARE @sql AS NVARCHAR(MAX)\r\n"
                       + "DECLARE curAddArticle CURSOR\r\n"
                       + "FOR\r\n"
                       + "    SELECT  'exec sp_addarticle @publication = N''" + PublicationName + "'', @article = N'''\r\n"
                       + "            + t.name + ''', @source_owner = N''' + s.name\r\n"
                       + "            + ''', @source_object = N''' + t.name\r\n"
                       + "            + ''', @type = N''logbased'', @description = null, @creation_script = null, @pre_creation_cmd = N''drop'', @schema_option = 0x0000000008035DDB, @identityrangemanagementoption = N''manual'', @destination_table = N'''\r\n"
                       + "            + t.name + ''', @destination_owner = N''' + s.name\r\n"
                       + "            + ''', @status = 16, @vertical_partition = N''false'', @ins_cmd = N''CALL sp_MSins_'\r\n"
                       + "            + s.name + t.name + ''', @del_cmd = N''CALL sp_MSdel_' + s.name\r\n"
                       + "            + t.name + ''', @upd_cmd = N''SCALL sp_MSupd_' + s.name + t.name\r\n"
                       + "            + '''' AS cmd\r\n"
                       + "    FROM    sys.tables t\r\n"
                       + "            INNER JOIN sys.schemas s ON s.schema_id = t.schema_id\r\n"
                       + "    WHERE   s.name = 'mdm' AND t.name NOT IN ( SELECT name FROM dbo.sysarticles ) -- check the tables that has not been added to the subscription yet\r\n"
                       + "    ORDER BY t.name\r\n"
                       + "OPEN curAddArticle\r\n"
                       + "FETCH NEXT FROM curAddArticle INTO @sql\r\n"
                       + "WHILE @@FETCH_STATUS = 0\r\n"
                       + "    BEGIN\r\n"
                       + "        PRINT @sql\r\n"
                       + "        EXEC (@sql)\r\n"
                       + "        WAITFOR DELAY '00:00:05';\r\n"
                       + "        FETCH NEXT FROM curAddArticle INTO @sql\r\n"
                       + "    END\r\n"
                       + "CLOSE curAddArticle\r\n"
                       + "DEALLOCATE curAddArticle\r\n"
                       + "PRINT 'Replication Add Article Complete.'\r\n"
                       + "GO\r\n";
            sb.Append(sql);
        }

        private void ReplicationDropArticle(StringBuilder sb, ActionEntity ae)
        {
            sb.Append("DECLARE @EntityTable NVARCHAR(100)\r\n");
            sb.Append("DECLARE @SecurityTable NVARCHAR(100)\r\n");
            sb.Append("SELECT @EntityTable = e.EntityTable, @SecurityTable = e.SecurityTable FROM mdm.tblModel m INNER JOIN mdm.tblEntity e ON e.Model_ID = m.ID WHERE e.MUID = '" + ae.ceDestination.EntityMUID + "'\r\n");
            sb.Append("\r\n");
            sb.Append("IF EXISTS (SELECT * FROM sysarticles a INNER JOIN dbo.syspublications p ON p.pubid = a.pubid WHERE a.name = @EntityTable AND p.Name = '" + PublicationName + "')\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("   PRINT 'Dropping article for EntityTable: ' + @EntityTable\r\n");
            sb.Append("   EXEC sp_dropsubscription @publication = '" + PublicationName + "', @article = @EntityTable, @subscriber = 'all'\r\n");
            sb.Append("   EXEC dbo.sp_droparticle  @publication = '" + PublicationName + "', @article = @EntityTable, @force_invalidate_snapshot = 1\r\n");
            sb.Append("END\r\n");
            sb.Append("ELSE\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("	PRINT 'Already dropped article for EntityTable: ' + @EntityTable\r\n");
            sb.Append("END\r\n");
            sb.Append("IF EXISTS (SELECT * FROM sysarticles a INNER JOIN dbo.syspublications p ON p.pubid = a.pubid WHERE a.name = @SecurityTable AND p.Name = '" + PublicationName + "')\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("   PRINT 'Dropping article for SecurityTable: ' + @SecurityTable\r\n");
            sb.Append("   EXEC sp_dropsubscription @publication = '" + PublicationName + "', @article = @SecurityTable, @subscriber = 'all'\r\n");
            sb.Append("   EXEC dbo.sp_droparticle  @publication = '" + PublicationName + "', @article = @SecurityTable, @force_invalidate_snapshot = 1\r\n");
            sb.Append("END\r\n");
            sb.Append("ELSE\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("	PRINT 'Already dropped article for SecurityTable: ' + @SecurityTable\r\n");
            sb.Append("END\r\n");
            
        }
        #endregion
        #endregion

    }
}

